Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

How to get value from a close and open parenthesis?

Shell Programming and Scripting


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 1 Week Ago
ernesto ernesto is offline
Registered User
 
Join Date: Sep 2015
Last Activity: 26 May 2017, 3:50 AM EDT
Posts: 18
Thanks: 7
Thanked 1 Time in 1 Post
How to get value from a close and open parenthesis?

Hi Gurus,

I have an input like the one below. What i wanted to achieved is to create a select statement based from that information

INPUT
Code:
Insert into table_name (col1,col2,col3,col4,col5,DATE1,DATE2,col6,col7,col8,col9,col10,col11) values (6752,14932156,24,'ALL','Staff',to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS'),'aso 4','device',148,null,null,null);

OUTPUT
Code:
SELECT * FROM table_name 
WHERE col1   = 6752
AND   col2   = 14932156
AND   col3   = 24
AND   col4   = 'ALL'
AND   col5   = 'Staff'
AND   DATE1  = to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND   DATE2  = to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS')
AND   col6   = 'aso 4'
AND   col7   = 'device'
AND   col8   = 148
AND   col9   is null
AND   col10  is null
AND   col11  is null;

need your inputs on this.

Thanks.

Last edited by rbatte1; 1 Week Ago at 06:35 AM.. Reason: Reverted to supplied as I had missed the point.
Sponsored Links
    #2  
Old Unix and Linux 1 Week Ago
rbatte1 rbatte1 is offline Forum Staff  
Root armed
 
Join Date: Jun 2007
Last Activity: 25 May 2017, 7:38 AM EDT
Location: Lancashire, UK
Posts: 3,102
Thanks: 1,301
Thanked 590 Times in 534 Posts
Hello ernesto,

I have a few to questions pose in response first:-
  • Is this homework/assignment? There are specific forums for these.
  • What have you tried so far?
  • What output/errors do you get?
  • What OS and version are you using?
  • What are your preferred tools? (C, shell, perl, awk, etc.)
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)
Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.


We're all here to learn and getting the relevant information will help us all.


Thanks, in advance,
Robin
Sponsored Links
    #3  
Old Unix and Linux 6 Days Ago
ernesto ernesto is offline
Registered User
 
Join Date: Sep 2015
Last Activity: 26 May 2017, 3:50 AM EDT
Posts: 18
Thanks: 7
Thanked 1 Time in 1 Post
hi,

no, this is not a homework. i am trying to create an automation tool where the inputs are from an sql file.

code is a per below.


Code:
echo "insert into table_name (col1,col2,col3,col4,col5,DATE1,DATE2,col6,col7,col8,col9,col10,col11) values (6752,14932156,24,'ALL','Staff',to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS'),'aso 4','device',148,null,null,null);" | while read file; do table=$(echo $file | awk -F"(" '{print $1}' | cut -d' ' -f3); cols=$(echo $file | awk -F"(" '{print $(NF - 3)}' | cut -d')' -f1); vals=$(echo $file | cut -d'(' -f3- | sed -e 's/);//g'); echo $table; echo $cols; echo $vals; done
table_name
col1,col2,col3,col4,col5,DATE1,DATE2,col6,col7,col8,col9,col10,col11
6752,14932156,24,'ALL','Staff',to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS'),'aso 4','device',148,null,null,null

    #4  
Old Unix and Linux 4 Days Ago
ernesto ernesto is offline
Registered User
 
Join Date: Sep 2015
Last Activity: 26 May 2017, 3:50 AM EDT
Posts: 18
Thanks: 7
Thanked 1 Time in 1 Post
after long hours. Finally got the code to work for this.


Code:
echo "insert into table_name (col1,col2,col3,col4,col5,DATE1,DATE2,col6,col7,col8,col9,col10,col11) values (6752,14932156,24,'ALL','Staff',to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS'),'aso 4','device',148,null,null,null);" | while read file; do table=$(echo $file | awk -F"(" '{print $1}' | cut -d' ' -f3); cols=$(echo $file | awk -F"(" '{print $(NF - 3)}' | cut -d')' -f1); vals=$(echo $file | cut -d'(' -f3- | sed -e 's/);//g'); echo $table; echo $cols; echo $vals; done
table_name
col1,col2,col3,col4,col5,DATE1,DATE2,col6,col7,col8,col9,col10,col11
6752,14932156,24,'ALL','Staff',to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS'),'aso 4','device',148,null,null,null);
while read line
do
tab=$(echo $file | cut -d' ' -f3)
value=$(echo $file | awk -F "values" '{print $2}' | sed -e 's/^ (//g' -e 's/);//g' -e "s/','MM/'|'MM/g")
cols=$(echo $file | awk -F"[()]" '{print $2}')
statement=$(echo -e "SELECT $cols FROM $tab $(paste -d"= " <(echo $cols | tr ',' '\n') <(echo $val |  sed -e 's/$/;/g' | tr ',' '\n') | sed -e '1s/^/WHERE /g' -e '2,100s/^/AND /g' | sed 's/=/ = /g' | sed -e 's/|/,/g')\n")

#Check a word null is present
#If yes, then need to change "= null" to "IS NULL"
if [[ $(echo ${statement} | grep -ic "null") -gt 0 ]]
then
   
   #We need to replace the line with null to "IS NULL"
   echo $statement | sed -e 's/= null/IS NULL/g'
fi

done

share if your have any other way to solve it
Sponsored Links
    #5  
Old Unix and Linux 3 Days Ago
RudiC RudiC is online now Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 28 May 2017, 4:46 AM EDT
Location: Aachen, Germany
Posts: 10,827
Thanks: 272
Thanked 3,324 Times in 3,061 Posts
I can't see your above code to be working as presented. Try instead

Code:
awk '
function CVBCK(STR)     {sub (/\]/, ")", STR)
                         sub (/;/, ",", STR)
                         sub (/\[/, "(", STR)
                         return STR
                        }

        {gsub (/to_date *\([^)]*\)/, "&\001")
         gsub (/to_date *\([^,]*,/, "&\001")
         gsub (/to_date *\(/, "&\001")
         gsub (/\(\001/, "[")
         gsub (/,\001/, ";")
         gsub (/\)\001/, "]")
         gsub (/null/, "IS NULL")
         sub ("Insert into", "SELECT * FROM", $1)
         print $1
         n = split ($2, C, ",")
         m = split ($4, V, ",")
         T = "WHERE"
         for (i=1; i<=n; i++)   {print T " " C[i] " " (V[i] !~ "IS"?"= ":"") CVBCK(V[i])
                                 T = "AND"
                                }
        }
' FS="[()]" file
SELECT * FROM table_name 
WHERE col1 = 6752
AND col2 = 14932156
AND col3 = 24
AND col4 = 'ALL'
AND col5 = 'Staff'
AND DATE1 = to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND DATE2 = to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS')
AND col6 = 'aso 4'
AND col7 = 'device'
AND col8 = 148
AND col9 IS NULL
AND col10 IS NULL
AND col11 IS NULL

You have to go quite some lengths to circumnavigate the difficulties arising from to_date using the same delimiters as does the entire statement - "(" and ")" - and the respective values in the fields - ",".
The Following User Says Thank You to RudiC For This Useful Post:
ernesto (2 Days Ago)
Sponsored Links
    #6  
Old Unix and Linux 2 Days Ago
ernesto ernesto is offline
Registered User
 
Join Date: Sep 2015
Last Activity: 26 May 2017, 3:50 AM EDT
Posts: 18
Thanks: 7
Thanked 1 Time in 1 Post
Hi RudiC,

Thanks for the reply. i just wanted to know how the code works.
Sponsored Links
    #7  
Old Unix and Linux 1 Day Ago
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 26 May 2017, 8:32 PM EDT
Posts: 2,016
Thanks: 19
Thanked 339 Times in 305 Posts

Code:
$ 
$ cat -n statements.sql
     1    Insert into table_name (col1,col2,col3,col4,col5,DATE1,DATE2,col6,col7,col8,col9,col10,col11) values (6752,14932156,24,'ALL','Staff',to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS'),'aso 4','device',148,null,null,null);
     2    Insert into emp (empno,ename,job,sal,hiredate,mgr,deptno) values (21,'Adrian','Salesman',1000,to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),1,42);
     3    Insert into tname (col1,col2,col3,col4) values (to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('02/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('03/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'),to_date('04/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS'));
     4    Insert into temp (col1,col2,col3,col4) values (null,null,null,null);
$ 
$ 
$ cat -n generate_qry.pl 
     1    #!/usr/bin/perl -w
     2    use strict;
     3    # Accept the file name as first argument and open it
     4    my $file = $ARGV[0];
     5    
     6    open(FH, "<", $file) or die "Can't open $file: $!";
     7    while (<FH>) {
     8        # Parse the line and set the table name, column-list and value-list
     9        my ($tab, $cols, $vals) = m/into\s+(\S+)\s+\((.*)\)\s+values\s+\((.*)\)/i;
    10    
    11        # Split the column-list and value-list into arrays. The "to_date" token will
    12        # be split into two elements and will be joined during loop iteration.
    13        my @c = split(/,/,$cols);
    14        my @v = split(/,/,$vals);
    15        my $qry = sprintf("select * from %s", $tab);
    16        my ($left, $right, $j);
    17    
    18        foreach my $i (0..$#c) {
    19            # Each predicate is of the form "left-string right-string". Here,
    20            # "left-string" is "where column" and "right-string" is "= value".
    21            if ($i eq 0) {
    22                $left = sprintf("where %s", $c[$i]);
    23                $j = $i;
    24            } else {
    25                $left = sprintf("and %s", $c[$i]);
    26            }
    27            $right = $v[$j];
    28    
    29            # Check the right-string and adjust it according to its value. If
    30            # it has "to_date" function, then increment the right counter and append
    31            # the next element.
    32            if ($right =~ m/null/i) {
    33                $right = sprintf(" is %s", $right);
    34            } elsif ($right =~ m/to_date/i) {
    35                $j++;
    36                $right = sprintf(" = %s,%s", $right, $v[$j]);
    37            } else {
    38                $right = sprintf(" = %s", $right);
    39            }
    40            $j++;
    41            $qry .= sprintf("\n%s%s", $left, $right);
    42        }
    43    
    44        # We're done. Print the query and move on to the next line from file.
    45        $qry .= ";\n";
    46        printf("\n%s", $qry);
    47    }
    48    close(FH) or die "Can't close $file: $!";
    49    
$ 
$ perl generate_qry.pl statements.sql

select * from table_name
where col1 = 6752
and col2 = 14932156
and col3 = 24
and col4 = 'ALL'
and col5 = 'Staff'
and DATE1 = to_date('04/17/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and DATE2 = to_date('05/24/2017 23:59:59','MM/DD/YYYY HH24:MI:SS')
and col6 = 'aso 4'
and col7 = 'device'
and col8 = 148
and col9 is null
and col10 is null
and col11 is null;

select * from emp
where empno = 21
and ename = 'Adrian'
and job = 'Salesman'
and sal = 1000
and hiredate = to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and mgr = 1
and deptno = 42;

select * from tname
where col1 = to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and col2 = to_date('02/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and col3 = to_date('03/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
and col4 = to_date('04/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS');

select * from temp
where col1 is null
and col2 is null
and col3 is null
and col4 is null;
$ 
$

Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Open/close of ports balamv IP Networking 1 05-06-2011 12:54 PM
Can DOS close an open file? quattro20v Windows & DOS: Issues & Discussions 2 01-01-2009 04:09 PM
Number of open files (Dir's) how to close? satyam90 UNIX for Advanced & Expert Users 1 11-07-2008 08:17 AM
close open files before remove xramm Solaris 9 09-14-2007 08:23 AM
File status - open/close ?? Pal UNIX for Dummies Questions & Answers 3 09-26-2001 03:54 PM



All times are GMT -4. The time now is 04:48 AM.