ksh program

manasa_vs · January 9, 2012, 7:29am

Hi,
I have a text file with multiple sql statements in which I have pick only the below select update and delete statements and do print as given below.
All the select,update,delete statements will be as same as ( subquery with ().all statements ending with ; ) the examples given below.

1) select some text 
 (select some text(select some text) 
 where some condition) 
where condition;
 
2)update some text where condition;
 
3)delete some text where condition;

For all the select,update and delete statements there should be corresponding "where" clause.
In 1st example all the select has it's corresponding where clause.
except one select statement inside(sub query) dose not have its where clause. in this case we should print out as "no where clause".

In 2nd input, an update statement should have a where clause.
If update statement does not have it then we should print out "no where clause".

Same as update for delete.

jayan_jay · January 9, 2012, 7:59am

while read line
do
        cnt=$(echo $line | tr ' ' '\n' | egrep "select|update|delete|where" | wc -l)
        echo "$line" | nawk -v var="$cnt" '{if(var%2==0){print $0" --> success"}else{print $0" --> fail"}}'
done < infile

manasa_vs · January 10, 2012, 1:53am

Thanks,
jay... i will try out the code and post the result.

---------- Post updated 01-10-12 at 12:23 PM ---------- Previous update was 01-09-12 at 08:04 PM ----------

Hi jay,
nawk is not supported on my unix.
hence its not able to perform the above commanda.can you please do the above logic using some other way?also can you please put the print as echo commands so that results can be seen on the screen?

jayan_jay · January 10, 2012, 1:55am

If nawk is not supported, try with awk (or) gawk ..

itkamaraj · January 10, 2012, 1:56am

try with awk ( instead of nawk )

manasa_vs · January 10, 2012, 3:29am

Thanks raj and jay.
It is working now.
but this is not the desired result. please let me know if you want more examples of input and output

the script is checking each and every line and printing success.

*   Subsystem..: INFORMATICA ETL LOGGING AND CONTROL --> success
 --> success
*   Written by.: Yana Ruegsegger --> success
 --> success
* --> success
 --> success
*   --------------------------------------------------------------------------- --> success
 --> success
*           M A I N T E N A N C E   L O G --> success
 --> success
*   --------------------------------------------------------------------------- --> success
 --> success
*   Date     |  Programmer   |  Description --> success
 --> success
*   --------------------------------------------------------------------------- --> success
 --> success
*   07/24/07 | Dan Rolls     | Modified procedure to use create parameters for --> success
 --> success
*            |               |  current logging strategy --> success
 --> success
*   --------------------------------------------------------------------------- --> success
 --> success
*/ --> success
 --> success
( --> success
 --> success
in_source_session_seq_no IN NUMBER, -- ld_ctl_seq_no of the job to be copied (default template) --> success
 --> success
in_target_session IN VARCHAR2,      -- session name for job to be created --> success
 --> success
in_job_name IN VARCHAR2,        -- project name for job to be created --> success
 --> success
in_folder_name IN VARCHAR2,         -- folder name for job to be created --> success
 --> success
in_workflow_name IN VARCHAR2,       -- workflow name for job to be created --> success
 --> success
in_data_source IN VARCHAR2,         -- source for job to be created --> success
 --> success
in_data_target IN VARCHAR2,         -- target for job to be created --> success
 --> success
in_table_name IN VARCHAR2          -- table name for job to be created --> success
 --> success
) --> success

jayan_jay · January 10, 2012, 3:53am

Pls post the entire input content ..

manasa_vs · January 10, 2012, 4:09am

jay
The input content vary from one text file to other text file.
but the seniors remains same as i have explained in the first post.

out of complete text file, which may contain function or procedure we need to pick up only the complete select or update or delete statements in the file and check if we are using where clauses for each statement.
all the statements are separated by ';' except sub queries.

for example we can consider below statements as the content:-

select some text
(select some text(select some text)
where some condition)
where condition;
some statements some statements some statements;
update some text where condition;
some statements some statements;
delete some text where condition;
update some text condition;
delete some text condition;

for the last 2 statements above it should echo as false because it dose not have where clause.

jayan_jay · January 10, 2012, 4:39am

Addition of few lines .. Try with this ..

nawk '/^select/,/;/' infile | xargs | tr ';' '\n' > outfile
nawk '/^update/,/;/' infile | xargs | tr ';' '\n' >> outfile
nawk '/^delete/,/;/' infile | xargs | tr ';' '\n' >> outfile
grep . outfile > outfile1
while read line
do
        cnt=$(echo $line | tr ' ' '\n' | egrep "select|update|delete|where" | wc -l)
        echo "$line" | nawk -v var="$cnt" '{if(var%2==0){print $0" --> success"}else{print $0" --> fail"}}'
done < outfile1
rm outfile outfile1

itkamaraj · January 10, 2012, 4:41am

$ tr '\n' ' ' < input.txt | nawk -F\; '{for(i=1;i<=NF;i++){if($i!~/where/){print $i}}}'                                                             
 some statements some statements some statements
 some statements some statements
 update some text condition
 delete some text condition

---------- Post updated at 03:11 PM ---------- Previous update was at 03:09 PM ----------

$ tr '\n' ' ' < test.txt | nawk -F\; '{for(i=1;i<=NF;i++){if($i!~/where/){printf("%s -->False\n",$i)}}}'                                           
 some statements some statements some statements -->False
 some statements some statements -->False
 update some text condition -->False
 delete some text condition -->False

---------- Post updated at 03:11 PM ---------- Previous update was at 03:11 PM ----------

$ tr '\n' ' ' < test.txt | nawk -F\; '{for(i=1;i<=NF;i++){if($i!~/where/){printf("%s -->False\n",$i)}else{printf("%s --> Success\n",$i)}}}'        
select some text  (select some text(select some text)  where some condition)  where condition --> Success
 some statements some statements some statements -->False
 update some text where condition --> Success
 some statements some statements -->False
 delete some text where condition --> Success
 update some text condition -->False
 delete some text condition -->False

jayan_jay · January 10, 2012, 4:46am

itkamaraj:

$ tr '\n' ' ' < test.txt | nawk -F\; '{for(i=1;i<=NF;i++){if($i!~/where/){printf("%s -->False\n",$i)}else{printf("%s --> Success\n",$i)}}}'        
select some text  (select some text(select some text)  where some condition)  where condition --> Success
 some statements some statements some statements -->False
 update some text where condition --> Success
 some statements some statements -->False
 delete some text where condition --> Success
 update some text condition -->False
 delete some text condition -->False

@kamaraj, for the highlighted line, the script has to throw fail scenario. Pls have a look ..

itkamaraj · January 10, 2012, 4:52am

we need to pick up only the complete select or update or delete statements in the file

all the statements are separated by ';' except sub queries.

so, i guess the code provided by me is correct

jayan_jay · January 10, 2012, 5:01am

@ manasa, Is kamaraj correct ??!!

manasa_vs · January 10, 2012, 7:26am

sorry for the late reply.. i am in a session. i wil test the code and post the result.

---------- Post updated at 05:43 PM ---------- Previous update was at 04:16 PM ----------

Kamraj,
the script is failing please see the below execution. also the script should not through success/fail for all the line it should pring success for only select/update/delete with where clauses and faile for only select/update/delete with out where clause.

      UPDATE PARM_TABLE       SET value = in\_job\_name||'_LOAD '       WHERE                                                                              seq_no --&gt;False
   COMMIT --&gt;False
END IF --&gt;False

itkamaraj:

$ tr '\n' ' ' < input.txt | nawk -F\; '{for(i=1;i<=NF;i++){if($i!~/where/){print $i}}}'                                                             
 some statements some statements some statements
 some statements some statements
 update some text condition
 delete some text condition

---------- Post updated at 03:11 PM ---------- Previous update was at 03:09 PM ----------

$ tr '\n' ' ' < test.txt | nawk -F\; '{for(i=1;i<=NF;i++){if($i!~/where/){printf("%s -->False\n",$i)}}}'                                           
 some statements some statements some statements -->False
 some statements some statements -->False
 update some text condition -->False
 delete some text condition -->False

---------- Post updated at 03:11 PM ---------- Previous update was at 03:11 PM ----------

$ tr '\n' ' ' < test.txt | nawk -F\; '{for(i=1;i<=NF;i++){if($i!~/where/){printf("%s -->False\n",$i)}else{printf("%s --> Success\n",$i)}}}'        
select some text  (select some text(select some text)  where some condition)  where condition --> Success
 some statements some statements some statements -->False
 update some text where condition --> Success
 some statements some statements -->False
 delete some text where condition --> Success
 update some text condition -->False
 delete some text condition -->False

---------- Post updated at 05:46 PM ---------- Previous update was at 05:43 PM ----------

Hi Jay,
with the above code out put is not getting displayed on the screen.
as your post says for all the lines in the file it is printing success or failure,
but it should print success/failure only for select,update and delete statements.

---------- Post updated at 05:56 PM ---------- Previous update was at 05:46 PM ----------

Till end of the file search for all the select/update/delete statements(all statements end with only and perform as below
{
if number of �selects�= number of �where� before statement end by ; then success
else failure
if number of �update�=number of � where� before ; of the statement then success
else failure
if number of �delete�=number of � where� before statement end by; then success
else failure
}

ygemici · January 10, 2012, 2:39pm

# cat yourfile
select some text
(select some text(select some text)
where some condition)
where condition;
some statements some statements some statements;
update some text where condition;
some statements some statements;
delete some text where condition;
update some text condition;
delete some text condition;

# awk '{gsub("\n","");}
{
if(match($0,"[(].*[)]"))
x=$0;while(match(x,"[(].*[)]")){
s=gensub(/.*\(((.[^)]*))\).*/,"\\1",$0);
if(s~/where/){print "["NR"]","is [[SUCCESS]] ->",s}
else{print "["NR"]","is [FALSE] ->",s;}x=gensub("[(]"s"[)]","",x);$0=x}
if($0~/^$/){next}if($0!~/select|delete|update|insert/){print "["NR"]","may not a SQL state ->",$0}
else{
if($0~/where/){print "["NR"]","is [[SUCCESS]] ->",$0;}
else{print "["NR"]","is [FALSE] ->",$0;}}
}' RS=';' yourfile
[1] is [FALSE] -> select some text
[1] is [[SUCCESS]] -> select some text where some condition
[1] is [[SUCCESS]] -> select some text  where condition
[2] may not a SQL state -> some statements some statements some statements
[3] is [[SUCCESS]] -> update some text where condition
[4] may not a SQL state -> some statements some statements
[5] is [[SUCCESS]] -> delete some text where condition
[6] is [FALSE] -> update some text condition
[7] is [FALSE] -> delete some text condition

regards
ygemici

Chubler_XL · January 10, 2012, 5:08pm

Nice solution ygemici,

I tweaked it a little to show numbered subqueries:

ygemici.awk:

{ gsub("\n", " "); $0=$0 }
{ 
if(match($0,"[(].*[)]")) x=$0
subq=1
while(match(x,"[(].*[)]")){
    s=gensub(/.*\(((.[^)]*))\).*/,"\\1",$0);
    if(s~/where/) {
            print "["NR "." subq "] is [[SUCCESS]] ->",s
        } else {
            print "["NR "." subq "] is [FALSE] ->",s
        }
        x=gensub("[(]"s"[)]"," <subquery-" NR "." subq "> ",x)
        subq++
        $0=x
}
if($0~/^$/) next
if($0!~/select|delete|update|insert/)
    print "["NR"]","may not a SQL state ->",$0
else {
    if($0~/where/) print "["NR"]","is [[SUCCESS]] ->",$0
    else print "["NR"]","is [FALSE] ->",$0
    }
}

# awk -f ygemici.awk RS=';' yourfile
[1.1] is [FALSE] -> select some text
[1.2] is [[SUCCESS]] -> select some text <subquery-1.1>  where some condition
[1] is [[SUCCESS]] -> select some text  <subquery-1.2>  where condition
[2] may not a SQL state ->  some statements some statements some statements
[3] is [[SUCCESS]] ->  update some text where condition
[4] may not a SQL state ->  some statements some statements
[5] is [[SUCCESS]] ->  delete some text where condition
[6] is [FALSE] ->  update some text condition
[7] is [FALSE] ->  delete some text condition

ygemici · January 11, 2012, 4:27am

chubler_xl:

Nice solution ygemici,

I tweaked it a little to show numbered subqueries:

ygemici.awk:

{ gsub("\n", " "); $0=$0 }
{ 
if(match($0,"[(].*[)]")) x=$0
subq=1
while(match(x,"[(].*[)]")){
   s=gensub(/.*\(((.[^)]*))\).*/,"\\1",$0);
   if(s~/where/) {
   print "["NR "." subq "] is [[SUCCESS]] ->",s
   } else {
   print "["NR "." subq "] is [FALSE] ->",s
   }
   x=gensub("[(]"s"[)]"," <subquery-" NR "." subq "> ",x)
   subq++
   $0=x
}
if($0~/^$/) next
if($0!~/select|delete|update|insert/)
   print "["NR"]","may not a SQL state ->",$0
else {
   if($0~/where/) print "["NR"]","is [[SUCCESS]] ->",$0
   else print "["NR"]","is [FALSE] ->",$0
   }
}

# awk -f ygemici.awk RS=';' yourfile
[1.1] is [FALSE] -> select some text
[1.2] is [[SUCCESS]] -> select some text <subquery-1.1>  where some condition
[1] is [[SUCCESS]] -> select some text  <subquery-1.2>  where condition
[2] may not a SQL state ->  some statements some statements some statements
[3] is [[SUCCESS]] ->  update some text where condition
[4] may not a SQL state ->  some statements some statements
[5] is [[SUCCESS]] ->  delete some text where condition
[6] is [FALSE] ->  update some text condition
[7] is [FALSE] ->  delete some text condition

it was a more great example now

manasa_vs · January 11, 2012, 7:10am

Hi,
I have the sql file of 250 to 300 lines in that some 10 or 20 statements wil ve select/update or delete. so i don't want to show the rest of the lines which is not select/update/delete on my window.
can you please modify the above code as such pelase

---------- Post updated at 04:51 PM ---------- Previous update was at 04:12 PM ----------

Both the scripts are working like Charm
but I have the sql file of 250 to 300 lines in that some 10 or 20 statements wil ve select/update or delete. so i don't want to show the rest of the lines which is not select/update/delete on my window.
can you please modify the above code as such pelase

---------- Post updated at 05:40 PM ---------- Previous update was at 04:51 PM ----------

one more think is insteard of wring the code in other file and calling it using awk

can we directly put the below line in the code its self and execute it?

 awk -f ygemici.awk RS=';' yourfile

ygemici · January 11, 2012, 3:59pm

manasa_vs:

Hi,
I have the sql file of 250 to 300 lines in that some 10 or 20 statements wil ve select/update or delete. so i don't want to show the rest of the lines which is not select/update/delete on my window.
can you please modify the above code as such pelase

---------- Post updated at 04:51 PM ---------- Previous update was at 04:12 PM ----------

Both the scripts are working like Charm
but I have the sql file of 250 to 300 lines in that some 10 or 20 statements wil ve select/update or delete. so i don't want to show the rest of the lines which is not select/update/delete on my window.
can you please modify the above code as such pelase

---------- Post updated at 05:40 PM ---------- Previous update was at 04:51 PM ----------

one more think is insteard of wring the code in other file and calling it using awk

can we directly put the below line in the code its self and execute it?
 awk -f ygemici.awk RS=';' yourfile

save to this a file for example as 'awk_sql.file'

{gsub("\n","");}
{
if($0~/select|delete|update/)
{
if(match($0,"[(].*[)]"))
{subq=1;
x=$0;while(match(x,"[(].*[)]"))
{
s=gensub(/.*\(((.[^)]*))\).*/,"\\1",$0);suba=" <subquery-" NR "." subq">"suba;
if(subq!=1){sub(">","",suba);suba=suba">"}
if(s~/where/){print "["NR "." subq "] is [[SUCCESS]] ->",s}
else{print "["NR "." subq "] is [[SUCCESS]] ->",s;}
x=gensub("[(]"s"[)]",suba,x);;subq++;$0=x;}
if($0~/where/){print "["NR"]","is [[SUCCESS]] ->",$0;}
else{
print "["NR"]","is [FALSE] ->",$0;}
}
else{
if($0~/where/){print "["NR"]","is [[SUCCESS]] ->",$0;}
else{print "["NR"]","is [FALSE] ->",$0;}
}
}
}

# awk -f awk_sql.file RS=';' yourinput

regards
ygemici

Chubler_XL · January 11, 2012, 4:31pm

manasa_vs:

one more think is insteard of wring the code in other file and calling it using awk

can we directly put the below line in the code its self and execute it?
awk -f ygemici.awk RS=';' yourfile

Write this to file process_sql.awk:

#!/usr/bin/awk -f
BEGIN { RS=";" }
{ gsub("\n", " "); $0=$0 }
{ 
  if(match($0,"[(].*[)]")) x=$0
  subq=1
  while(match(x,"[(].*[)]")){
    s=gensub(/.*\(((.[^)]*))\).*/,"\\1",$0);
    if(s~/where/) {
            print "["NR "." subq "] is [[SUCCESS]] ->",s
        } else {
            print "["NR "." subq "] is [FALSE] ->",s
        }
        x=gensub("[(]"s"[)]"," <subquery-" NR "." subq "> ",x)
        subq++
        $0=x
  }
  if($0~/^$/ || $0!~/select|delete|update|insert/) next
  if($0~/where/) print "["NR"]","is [[SUCCESS]] ->",$0
  else print "["NR"]","is [FALSE] ->",$0
}

Then make sure you set file as executable chmod 755 process_sql.awk

and call it like this:

$ process_sql.awk yourinput
[1.1] is [FALSE] -> select some text
[1.2] is [[SUCCESS]] -> select some text <subquery-1.1>  where some condition
[1] is [[SUCCESS]] -> select some text  <subquery-1.2>  where condition
[3] is [[SUCCESS]] ->  update some text where condition
[5] is [[SUCCESS]] ->  delete some text where condition
[6] is [FALSE] ->  update some text condition
[7] is [FALSE] ->  delete some text condition