awk - split data based on the count

Shell Programming and Scripting
1

Greetings Experts,
I am generating a validation query through awk and facing an issue, which I need to overcome by splitting the data based on the pattern matching count in the value of an array.

File1  -- 
Table11@column1@date@Table21@column1@varchar(10)@d;
Table11@column2@varchar@Table21@column2@date@d;
Table11@column3@varchar@Table21@column3@date@d;
............
Table11@column100@varchar@Table21@column100@date@d;
Table11@column101@varchar@Table21@column101@date@d;
............
Table11@column320@integer@Table21@column320@date@d;
............
Table19@column1@integer@Table29@column1@varchar@o;
Table19@column2@integer@Table29@column2@varchar@b;
Table19@column3@integer@Table29@column3@varchar@o;

Here Table11 can contain more than 100 columns with type as "d" or "o" which is specified in the last column i.e., there can be more than 100 entries in the combination of column1, column4, column7 there by making it more than 100 entries in the file. My awk script is

awk -F "@" 'BEGIN { RS=";" ; OFS=","}
{
str="SUM(CASE WHEN T1." $2 "=T2." $5 " THEN 1 ELSE 0 END) " $2"_"$5
a[$1 OFS $4 OFS $7]=(a[$1 OFS $4 OFS $7] ? a[$1 OFS $4 OFS $7] ", \n" str : str
}
END {
for (i in a)
{
k=split(i,b,",")
print "SELECT " a " FROM "b[1] " T1 \n INNER JOIN " b[2] " T2 \n ON T1.COLABC=T2.COLABC;" > "file_output.txt"
} }' File1.txt

I am getting the output as below for the combination of Table11, Table21,d

SELECT SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2,
......
SUM(CASE WHEN T1.column320=T2.column320 THEN 1 ELSE 0 END) column320_column320 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

But as there is a restriction that I cannot export more than 100 columns in a single select (Teradata Bteq export restriction), I need to break down the contents of array a in case if there are more than 100 entries based on the column1, column4, column7 i.e.,

SELECT SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2,
......
SUM(CASE WHEN T1.column100=T2.column100 THEN 1 ELSE 0 END) column100_column100 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

SELECT SUM(CASE WHEN T1.column101=T2.column101 THEN 1 ELSE 0 END) column101_column101,
SUM(CASE WHEN T1.column102=T2.column102 THEN 1 ELSE 0 END) column102_column102,
......
SUM(CASE WHEN T1.column200=T2.column200 THEN 1 ELSE 0 END) column200_column200 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

.....

SELECT SUM(CASE WHEN T1.column301=T2.column301 THEN 1 ELSE 0 END) column301_column301,
SUM(CASE WHEN T1.column302=T2.column302 THEN 1 ELSE 0 END) column302_column302,
......
SUM(CASE WHEN T1.column320=T2.column320 THEN 1 ELSE 0 END) column320_column320 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

I am confused on how to handle this with in awk as I need to complete this task with in awk as there are several tables involved which can be processed easily with the array structure in awk. At first I thought of using another array to count++ for column1, column4, column7 combination and write a loop; I need to think on this; or make another array in the combination of x[$1 OFS $4 OFS $7 OFS cnt++] where the variable cnt would be specific to each combination of $1 OFS $4 OFS $7 ; I am not sure how to do this as the array may not be processed in the increasing order of cnt for the combination of $1 OFS $4 OFS $7 Another alternative is to make a comparison on the contents of the array to check if there are more than 100 SUM(CASE If yes, then split them and process; I am stuck up badly on this and in need of your help greatly. Thank you for your time..

2

x[$1 OFS $4 OFS $7 OFS cnt++] does not make sense.
A counter for each col1,col4,col7 combination is cnt[$1 OFS $4 OFS $7]++ i.e. every cnt element is a counter.

3

Hi MadeInGermany,

Thank you for the suggestion. Creating one more array to count the combination of $1 OFS $4 OFS $7 and then I need to find a way to go through this algorithm

for (i in a) {
k=split(i,b,",")
for (z=1; z<=cnt; (z+100))  {
y=get the z to z+99 SUM(CASE matching ones here
print "SELECT " y " FROM "b[1] " T1 \n INNER JOIN " b[2] " T2 \n ON T1.COLABC=T2.COLABC;" > "file_output.txt"
}
}

I don't know how to get the z to z+99 matching SUM(CASE from the array a with in the loop. Can someone kindly explain how to achieve this. Please note that storing every part of z to z+99 in another array++ is not feasible for my script as that makes the script dependent on the data on the combination of $1 OFS $4 OFS $7

4

That was a hell of a specification to understand - not sure I did to its entirety. Anyway, try

awk -F "@" '
BEGIN   {FMT1 = "%s%s\tSUM(CASE WHEN T1.%s=T2.%s THEN 1 ELSE 0 END) %s_%s"
         FMT2 = "\nFROM %s T1 INNER JOIN %s T2 ON T1.COLABC=T2.COLABC;\n\n"
        }
        {IX = $1 "," $4 "," $7
         ARR[IX "," ++CNT[IX]] = $2 "_" $5
        }
END     {for (c in CNT) {split  (c, TBL, ",")
                         printf "SELECT "
                         LC = 0
                         LF = DL = ""
                         for (i=1; i<=CNT[c]; i++)      {split (ARR[c "," i], COL, "_")
                                                         printf FMT1, DL, LF, COL[1], COL[2], COL[1], COL[2]
                                                         DL = ","
                                                         LF = "\n"
                                                         if (!(++LC%LNMX) &&
                                                                i<CNT[c])       {printf FMT2, TBL[1], TBL[2] 
                                                                                 printf "SELECT "
                                                                                 DL = LF = ""
                                                                        }
                                                        }
                         printf FMT2, TBL[1], TBL[2] 
                        }
        }
' LNMX=3 file
SELECT 	SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
	SUM(CASE WHEN T1.column3=T2.column3 THEN 1 ELSE 0 END) column3_column3
FROM Table19 T1 INNER JOIN Table29 T2 ON T1.COLABC=T2.COLABC;

SELECT 	SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2
FROM Table19 T1 INNER JOIN Table29 T2 ON T1.COLABC=T2.COLABC;

SELECT 	SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
	SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2,
	SUM(CASE WHEN T1.column3=T2.column3 THEN 1 ELSE 0 END) column3_column3
FROM Table11 T1 INNER JOIN Table21 T2 ON T1.COLABC=T2.COLABC;

SELECT 	SUM(CASE WHEN T1.column100=T2.column100 THEN 1 ELSE 0 END) column100_column100,
	SUM(CASE WHEN T1.column101=T2.column101 THEN 1 ELSE 0 END) column101_column101,
	SUM(CASE WHEN T1.column320=T2.column320 THEN 1 ELSE 0 END) column320_column320
FROM Table11 T1 INNER JOIN Table21 T2 ON T1.COLABC=T2.COLABC;

Set the max No. of lines LNMX to 100 if happy with the result and dealing with your large files...

1 Like
5

That was absolutely brilliant. Thank you RudiC :slight_smile: