Passing Arrays in AWK script / Calling SQL from AWK

[mjm22]

Hi

I have an AWK script within a unix script. Within the AWK script I wish to evaluate the value of a price column $5 based on the value in a rec type column $4 to try to determine if the price is equal to the minimum price for this call type.

I have an Oracle table that stores minimum prices for all rec types..

TYPE MIN_PRICE
NNN 20
XXX 15
YYY 25

I thought the best way would be to have an associative array containing the rec type as the index and the min price as the value. I could then count the number of records that have the min price set.

I need to get the data from the oracle table into the Associate Array. I thought I could call an PL/SQL query like I could in the main body of the unix script however I was unable to call any SQL from within the AWK script, maybe I am missing something - is this possible?

So next I decided to pass in the data to the awk script via parameters in the form of two unix arrays. However I don't seem to be able to pass these arrays into AWK. I have copied the code below. If anyone has any ideas how I can get this Array into AWK ( any method) then I would be grateful.

#!/bin/ksh
#use pl/sql to get the min call prices for all call types
RETVAL=`sqlplus -s $DATABASE <<EOF
set serveroutput on feedback off
DECLARE
priceList VARCHAR2(255);

CURSOR getMinPrices IS
SELECT rec_type, min_price
FROM min_price;

BEGIN
FOR v_rec IN getMinPrices LOOP
priceList := priceList || ' ' || v_rec.rec_type || ',' || v_rec.min_price;
END LOOP;
DBMS_OUTPUT.PUT_LINE(priceList);
END;
/
EXIT;
EOF`

#note this returns data in format NNN,20 XXX,15 YYY,25 I then separate this data into two arrays since I don't
#think I can have associative arrays in unix shell.

#first array is for rec types and holds NNN,XXX,YYY as the values, it is called rec_arr

#second array is for min price and holds 20,15,25 and is called price_arr

#I then want to call nawk, passing in my two arrays where I can merge the arrays into an associative array for
#processing..
nawk -F, -v ARR1=&quot;$rec_arr&quot; -v ARR2=&quot;$price_arr&quot; '
BEGIN {

....

#run a loop here to merge arrays into associative array called
#arrMinPrice (rec type as the index and min price as the value)....

#count number of records that have a price matching the min price for their record type..
if (arrMinPrice[$4] == $5)
{
totalAtMin++
}

.....
}'

+++++++++++

If anyone can help out I would be very grateful.

Thanks

Mike

 

[Ygor]

You could use the split function to create two temporary arrays and then a loop to build the associative array...

nawk -F, -v ARR1=&quot;$rec_arr&quot; -v ARR2=&quot;$price_arr&quot; '
BEGIN {
split(ARR1,arrTmp1,&quot;,&quot;
split(ARR2,arrTmp2,&quot;,&quot;
for (i in arrTmp1)
arrMinPrice[arrTmp1] = arrTmp2;
}'

 

[Ygor]

It's simpler to pass RETVAL as a single array...

nawk -v ARR1=&quot;$RETVAL&quot; '
BEGIN {
x=split(ARR1,arrTmp,&quot;[ ,]&quot;
for (i=1; i<x ;i+=2)
arrMinPrice[arrTmp] = arrTmp[i+1];
}'

 

[PHV]

Ygor, some problem with TGML ?

nawk -v ARR1="$RETVAL" '
BEGIN {
  x=split(ARR1,arrTmp,"[ ,]");
  for(i=1;i<x;i+=2)
    arrMinPrice[arrTmp[i]]=arrTmp[i+1];
}'

Hope This Help
PH.

 

[Ygor]

Thanks PHV! That's what I meant the code to be.