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[table] = substr("abcdefghijklmnopqrstuvwxyz", TablesCount, 1);
    }
    return TablesIds[table];
}

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[i];
            id    = getTableId(table);
            from  = from fsep table " " id;
            fsep  = ", "
        }
        
        #
        # Print SQL statement
        #
        
        print "\nselect",select,"\nfrom  ",from,"\nwhere ",where,";";

    }
 }
' jam.dat

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|

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 ;

generate_sql.ksh > request.sql