Try this new version :
nawk -F'|' -v Q="'" '
{
req = $1;
table = $3;
column = $4;
attribute = $5;
nbTbl = ++Requests[req]; # Tables count
Tables[req, nbTbl] = toupper(table);
Columns[req, nbTbl] = column;
Attributes[req, nbTbl] = attribute;
}
function getTableId(table) {
table = toupper(table);
if ( ! (table in TablesIds) ) {
Tables[++TablesCount] = table;
TablesIds = substr("abcdefghijklmnopqrstuvwxyz", TablesCount, 1);
}
return TablesIds;
}
END {
for (req in Requests) {
select = "";
where = "";
from = "";
#
# Fixed part of select columns statement
#
id = getTableId("V_PRT_CLASS");
select = id ".V_PRT_NBR";
id = getTableId("V_VMF_CLASS");
select = select ", " id ".V_VMF_NAME";
#
# Select columns and where clause from request definition
#
req_len = Requests[req];
wsep = "";
for (i=1; i<=req_len; i++) {
id = getTableId(Tables[req, i]);
select = select ", " id "." Columns[req, i];
attribute = Attributes[req, i];
if (attribute ~ /[*%]/) {
not = "";
if (substr(attribute,1,1) == "!") {
not = "not ";
attribute = substr(attribute,2);
}
where = where wsep id "." Columns[req, i] " " not "like " Q attribute Q;
} else {
where = where wsep id "." Columns[req, i] "=" Q attribute Q;
}
wsep = "\n and ";
}
#
# Fixed part of where clause
#
id1 = getTableId("V_PRT_CLASS");
id2 = getTableId("V_VMF_CLASS");
id3 = getTableId("V_RES_CLASS");
id4 = getTableId("V_RES_NW_CLASS");
where = where wsep id1 ".OBJ_ID=" id3 ".OBJ_ID";
where = where wsep id3 ".OBJ_ID=" id4 ".OBJ_ID";
where = where wsep id1 ".V_PRT_MFG_PTR=" id2 ".OBJ_ID";
#
# From clause
#
fsep = "";
for (i=1; i<=TablesCount; i++) {
table = Tables;
id = getTableId(table);
from = from fsep table " " id;
fsep = ", "
}
#
# Print SQL statement
#
print "\nselect",select,"\nfrom ",from,"\nwhere ",where,";";
}
}
' jam.dat
Inputfile:
R1|Array/Network Resistor - VIP|V_RES_CLASS|V_MOUNT_FEATURE|SURFACE MOUNT|AND|8533.10.00.20|8533.10.00.20|
R1|Array/Network Resistor - VIP|V_RES_NW_CLASS|V_TECHNOLOGY|CARBON*||8533.10.00.20|8533.10.00.20|
R4|Array/Network Resistor - VIP|V_RES_CLASS|V_MOUNT_FEATURE|THROUGH HOLE MOUNT|AND|8533.10.00.57|8533.10.00.57|
R4|Array/Network Resistor - VIP|V_RES_NW_CLASS|V_TECHNOLOGY|CARBON*||8533.10.00.57|8533.10.00.57|
R7|Array/Network Resistor - VIP|V_RES_CLASS|V_MOUNT_FEATURE|SURFACE MOUNT|AND|8533.21.00.10|8533.21.00.25|
R7|Array/Network Resistor - VIP|V_RES_NW_CLASS|V_TECHNOLOGY|!CARBON*|AND|8533.21.00.10|8533.21.00.25|
R7|Array/Network Resistor - VIP|V_PRT_CLASS|V_MFR_PKG_DESC|DIP*||8533.21.00.10|8533.21.00.25|
Output:
select a.V_PRT_NBR, b.V_VMF_NAME, c.V_MOUNT_FEATURE, d.V_TECHNOLOGY
from V_PRT_CLASS a, V_VMF_CLASS b, V_RES_CLASS c, V_RES_NW_CLASS d
where c.V_MOUNT_FEATURE='SURFACE MOUNT'
and d.V_TECHNOLOGY like 'CARBON*'
and a.OBJ_ID=c.OBJ_ID
and c.OBJ_ID=d.OBJ_ID
and a.V_PRT_MFG_PTR=b.OBJ_ID ;
select a.V_PRT_NBR, b.V_VMF_NAME, c.V_MOUNT_FEATURE, d.V_TECHNOLOGY
from V_PRT_CLASS a, V_VMF_CLASS b, V_RES_CLASS c, V_RES_NW_CLASS d
where c.V_MOUNT_FEATURE='THROUGH HOLE MOUNT'
and d.V_TECHNOLOGY like 'CARBON*'
and a.OBJ_ID=c.OBJ_ID
and c.OBJ_ID=d.OBJ_ID
and a.V_PRT_MFG_PTR=b.OBJ_ID ;
select a.V_PRT_NBR, b.V_VMF_NAME, c.V_MOUNT_FEATURE, d.V_TECHNOLOGY, a.V_MFR_PKG_DESC
from V_PRT_CLASS a, V_VMF_CLASS b, V_RES_CLASS c, V_RES_NW_CLASS d
where c.V_MOUNT_FEATURE='SURFACE MOUNT'
and d.V_TECHNOLOGY not like 'CARBON*'
and a.V_MFR_PKG_DESC like 'DIP*'
and a.OBJ_ID=c.OBJ_ID
and c.OBJ_ID=d.OBJ_ID
and a.V_PRT_MFG_PTR=b.OBJ_ID ;
if you want to write the result of the script (for example named 'generate_sql.ksh') in the file 'request.sql' :
generate_sql.ksh > request.sql
I don't understand what you want to do.
Give us an example.
Jean-Pierre.