Shell Programming and Scripting

Hi,

Please help me in following prob.

I have a input file in which UPDATE statements will be present. I need to check the count of the rows impacted by each statement. I am using below code to do so:

$dml --> File having UPDATE SQLs like
Update <table_name> Set <field>=<value> where <field>=<value>

$temp1 --> Temporary file having only WHERE clause of the SQLs present in $dml

$ddl --> Having SELECT COUNT(*) statements using the WHERE clause present in $temp1

Code:

cnt=`cat $dml | grep -c "where"  | tr -s " "` 
sed 's/.*\(where .*$\)/\1/' $dml > $temp1 
sed "s/where/select count(*) from $SCH.$TBL where/" $temp1 > $ddl

ISSUE: If the SQL in the input file ($dml) have WHERE or Where instead of where then this code does not work.


So I need to make it case insensitive....
I can make "cnt=`cat $dml | grep -c "where" | tr -s " "` " case insensitive by adding -i in grep:
cnt=`cat $dml | grep -c -i "where" | tr -s " "` .


Please suggest me if this is wrong and I need to make the rest of the 2 commands case insensitive...

sed 's/.*\(where .*$\)/\1/' $dml > $temp1
sed "s/where/select count(*) from $SCH.$TBL where/" $temp1 > $ddl

Code:

cnt=`cat $dml | grep -c "where"  | tr -s " "` 
sed 's/.*\([wW][hH][eE][rR][eE] .*$\)/\1/' $dml > $temp1 
sed "s/[wW][hH][eE][rR][eE]/select count(*) from $SCH.$TBL where/" $temp1 > $ddl

thanks...
this is working...

No temp file generated.

Code:

cnt=`cat $dml | grep -c "where"  | tr -s " "` 
awk -v s=$SCH -v t=TBL 'tolower($(NF-1))=="where" {$0="select count(*) from " t "." s " where " $NF}1'  $dml >  $ddl

---------- Post updated at 11:19 AM ---------- Previous update was at 09:00 AM ----------

if you have gawk and support IGNORECASE=1

Code:

awk -v s=$SCH -v t=TBL 'BEGIN{IGNORECASE=1}/where/{$0="select count(*) from " t "." s " where " $NF}1'  $dml