awk with variable from sqlplus

Shell Programming and Scripting
1

Hi, I'm a it stuck on the below code where a variable is pulled from sqlplus and used in awk. It runs with no errors but still pulls back all records in the input file.

It should pull the max reference from sql plus and then only print those records where the reference value in column 1 is greater than the max reference value from the db.

Input file is a comma delimited and reference is a 14 digit number.

Thanks for any help in pointing me in the right direction!

value=`sqlplus -s user/pass << EOF
      set heading off;
      select max(reference)  from cred_table;
      exit;
EOF`

awk -F "," -v ref=$value'{if ($1 >= ref) printf("%-16s%-20s%-50s%-20s%-60s%-12s%-9s%-20s%-20s%-20s%-30s%-20s%-20s%-20s%-20s%-50s%-20s%-50s%-20s%-20s%-100s%-4s%-4s%-20s%-20s%-10s%-10s%-10s%-25s%-25s%-100s%-40s%-20s%-20s%-40s%-20s%-20s%-25s%-3s%-10s%-10s%-20s%-10s%-1s%-10s%-4s%-4s%-4s%-4s%-3s%-4s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s\n",$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61)}' appfile_$Day.out > C_LIQ_CR_APP_RT_$Day.DAT
2

Can you wrap variable: $value in double quotes and give it another try?

awk -F, -v ref="$value" ' { if ($1 >= ref) ...

If that does not help, post sample content from your input file: appfile_$Day.out in code tags

3

Wrapping the variable in quotes caused an error:

awk: String  cannot contain a newline character.
 The source line is 1.
 The error context is
                 >>>  <<<
 awk: String 2013057090 cannot be longer than 399 bytes.
 The source line is 1.
 awk: String 2013057090 cannot be longer than 399 bytes.
 The source line is 1.

Here is a sample of the appfile_$Day.out, I'm only including the first couple columns but it has 61 columns.

20130570900508,I,kevin,,
20130570900507,I,ARTUR,,
20130570900506,I,Rachel

Also for this test the value selected from sqlplus is 20130570900508

And by the way I'm running Unix on AIX 1

---------- Post updated at 10:45 PM ---------- Previous update was at 10:35 PM ----------

I also wanted to point out I'm able to echo the variable from sqlplus and it looks ok

echo $value

20130570900508

I've tried ltriming it and substring to 14 just to make sure there are no spaces in it. The field format in Oracle is character but I also tried a temp table with the value in numeric format.

4

Put below line instead of set heading off; and try

set echo off head off feed off pagesize 0 trimspool on linesize 1000;

BTW I don't see any issues with your awk code.

5

Yes that worked perfectly. Thanks for your help!