Problem in awk

Shell Programming and Scripting
1

Hi, I have a file (abc1.dat) with first field as table names and second field as previous months count and 3rd field as current months count. There are about 40 tables names in the file. This is the script I've written to find the percentage deviation for the two months.


cat ${workdir}/abc1.dat|awk '{ 
i = 0 
if ( $2 > $3 ) 
	 {
	 diff = $2 - $3 
	 i = 1
	 pd = ((($2 - $3) / $3) * 100)
	 } 
else if ( $3 > $2 ) 
	 {
	 diff = $3 - $2 
	 i = 1
	 pd = ((($3 - $2) / $2) * 100) 
	 } 

bteq << !!

.run file /db/d/lib/logon.script;

.SET SIDETITLES ON;
.SET TITLEDASHES OFF;

UPDATE AB_TABLE_${YYYY}
SET PD_ABC=$pd 
WHERE MONTHLY_TABLE_NAME LIKE '$1';

.QUIT; 

!!


errpct = $2 * 0.05 
if ( (i == 1) && (diff > errpct) ) 
{ printf "%-30s %-30d %-30d\n", $1, $2, $3 }
}' >> ${logdir}/errortables.log

The abc1.dat file looks like this

DB_YYYYMM                              3200867     3212612
DB_MASTER_YYYYMM                  5635623     5653570
DB_II_YYYYMM                           3200104     3211480
DB_MM                                      5635623     5653570
DB_I_YYYYMM                            5331904     5353944
DB_RAW_YYYYMM                       5723946     5740122

The error its showing is in the awk.

awk: Syntax error near line 16
awk: illegal statement near line 16
awk: illegal statement near line 18
awk: Syntax error near line 18
awk: bailing out near line 18

Please help me to solve this problem.
Thanks,
RRVARMA

2

Is this a typo o you are trying to mix shell and awk code?
You should remove the below lines from the awk code:

bteq << !!

.run file /db/d/lib/logon.script;

.SET SIDETITLES ON;
.SET TITLEDASHES OFF;

UPDATE AB_TABLE_${YYYY}
SET PD_ABC=$pd 
WHERE MONTHLY_TABLE_NAME LIKE '$1';

.QUIT; 

!!
3

Thanks Radoulov, but how can i update the table..?? my intention is to update the table with those values for the corresponding table names. Please help.:confused:

4

Build the SQL statements with the AWK print statement and pass them to your database. Don't try to run them inside AWK.

5

But how to connect to the database in awk. can u help me with this as well.. please.. :frowning:

6

Sorry, I don't seem to know that particular database software (looking at your code above ...), perhaps someone else could help ...

7

ok.. thank you very much for helping in knowing wat is the error atleast.. :slight_smile: