I have a very weird requirement and have no clue to resolve the issue. please help me get out this difficulty
below two tables, table1 contains the column name. D means this column used for the rule. for example: rule 0 is all columns have value, rule1 is col3 and col7 have no value. the case statement is based on table1 key order. 0 first, then 1...
table2 contains the column values.
the requirement is: based on table2 listed value write a case statement
below data the case statement is
case when COL1 = 'V' AND COL2 = 'B' AND COL3 = 'C' AND P_CODE = 0 AND M_SRC = 5 AND M_FEC = 0 AND SEC_TYP = 1 THEN 1
WHEN COL1 = 'V' AND COL2 = 'B' AND P_CODE = 0 AND M_SRC = 5 AND M_FEC = 0 THEN 2
WHEN COL1 = 'V' AND COL2 = 'I' AND P_CODE = 0 AND M_FEC = 0 THEN 3
WHEN COL1 = 'F' AND COL2 = 'B' AND P_CODE = 0 AND M_SRC = 5 AND M_FEC = 0 THEN 4
END
table1
key SRC1 SRC2 SRC3 SRC4 SRC5 SRC6 SRC7
0 COL1 COL2 COL3 P_CODE M_SRC M_FEC SEC_TYP
1 COL1 COL2 D P_CODE M_SRC M_FEC D
2 COL1 COL2 COL3 D M_SRC D D
3 COL1 COL2 D D M_SRC M_FEC SEC_TYP
4 COL1 COL2 D D M_SRC D SEC_TYP
table2
COL1 COL2 COL3 P_CODE M_SRC M_FEC SEC_TYP RESULT
V B C 0 5 0 1 1
V B 0 6 0 2
V I 0 0 3
F B 0 5 0 4
Is the requirement "For every rule in table 1 scour table 2 for one (or more?) matching records and print out one case in the case statement"? Match means identical yes / no (value / empty) patterns between the two tables. As line 2 and 4 in table 2 have the same yes / no pattern, you want two cases printed?
The third case statement cannot be produced with any of the keys in table 1.
Why do you need table 1 at all? The case statement shown can be produced from table 2 alone.
Is it correct that keys 2,3, and 4 are not applicable to table 2 shown as P_CODE is not empty in any record?
Why, in the second case, is M_SRC = 5 and not 6 as in table 2?
Do you really mean "D means this column used for the rule", or the opposite?
awk '
BEGIN {print "case"
}
NR == 1 {for (i=1;i<=NF; i++) COL = $i
next
}
{for (i=1; i<=NF; i++) {if ($i != "") printf "%s %s = %s", i==1?"WHEN ":i==NF?" THEN ":" AND", COL, $i
}
printf ORS
}
' FS="\t" file
case
WHEN COL1 = V AND COL2 = B AND COL3 = C AND P_CODE = 0 AND M_SRC = 5 AND M_FEC = 0 AND SEC_TYP = 1 THEN RESULT = 1
WHEN COL1 = V AND COL2 = B AND P_CODE = 0 AND M_SRC = 6 AND M_FEC = 0 THEN RESULT = 2
WHEN COL1 = V AND COL2 = I AND P_CODE = 0 AND M_FEC = 0 THEN RESULT = 3
WHEN COL1 = F AND COL2 = B AND P_CODE = 0 AND M_SRC = 5 AND M_FEC = 0 THEN RESULT = 4
You need to make very sure that empty fields are recognized by e.g. using an explicit field separator <TAB>.