create sql query


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting create sql query
# 8  
Old 07-07-2011
Hi Aigles,

My requierement is is....

i need to print column1|Sql Query|column7|column8 as the output of this awk script.

like below
Code:
R1|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 ;|8533.10.00.20|8533.10.00.20|

Also in query instead of , in the select attribute it should be ||'|'||

like below
Code:
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 ;

and * should be replaced with %

Thanks a ton for your quick help

Last edited by Franklin52; 07-07-2011 at 03:24 AM.. Reason: Please use code tags for code and data samples, thank you
# 9  
Old 07-07-2011
Try this new version :
Code:
nawk -F'|' -v Q="'" -v OFS="|" '

{
    req       = $1;
    table     = $3;
    column    = $4;
    attribute = $5;
    Column7[req] = $7;
    Column8[req] = $8;
    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 " || " Q "|" Q " || " 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 " || " Q "|" Q " || " id "." Columns[req, i];
            attribute = Attributes[req, i];
            if (attribute ~ /[*%]/) {
                gsub(/\*/, "%", 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 = "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 req, "\"select " select " from " from " where " where " ;\"", Column7[req],Column8[req];

    }
 }
' jam.dat

Inputfile (jam.dat):
Code:
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:
Code:
R1|"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_IDand c.OBJ_ID=d.OBJ_IDand a.V_PRT_MFG_PTR=b.OBJ_ID ;"|8533.10.00.20|8533.10.00.20
R4|"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_IDand c.OBJ_ID=d.OBJ_IDand a.V_PRT_MFG_PTR=b.OBJ_ID ;"|8533.10.00.57|8533.10.00.57
R7|"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_IDand c.OBJ_ID=d.OBJ_IDand a.V_PRT_MFG_PTR=b.OBJ_ID ;"|8533.21.00.10|8533.21.00.25

Jean-Pierre.
# 10  
Old 07-11-2011
Hi aigles,

Thanks a ton for your help.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Solaris

SQL QUERY to Table Output

Hi I am trying to run sql query from solaris in csh script and send the output to email. Below is my sql query select p.spid,se.program seprogram, se.machine, se.username, sq.sql_text,sq.retrows from v$process p inner join v$session se on p.addr = se.paddr inner join ( select... (2 Replies)
Discussion started by: tharmendran
2 Replies

2. Programming

Help writing SQL query

Hello All, I hope I'm posting this in the right section. I have zero sql query writing skill, in fact, I've never done it before, but for some reason, a request came across my desk to get information from one of our databases. I have about 200 ticket numbers that have no information attached,... (8 Replies)
Discussion started by: bbbngowc
8 Replies

3. Programming

Getting error in sql query

Hi All , I have tried many times am getting syntax error on 'UNION' can anybody tell me ... INSERT INTO table1 ( Type , num_items , num_letters , total_value ) (select type='1', num_items, num_letters=count(*), total_value=sum(letter_value) from table2 where num_items = 1 (1 Reply)
Discussion started by: Venkatesh1
1 Replies

4. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

5. Shell Programming and Scripting

problem in SQL query

I used the following code code select * from tablename where columnname Instead of printing the expected output it prints all the files in the present directory since there is a "*" in the code. Is there any way to overcome the problem? Thanks Ananth (2 Replies)
Discussion started by: Ananthdoss
2 Replies

6. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

7. Shell Programming and Scripting

sql query problem

Hi, I am passing an argument for the script and that argument values should exist in database. bill_period_input="'""$1""'" bill_period=`sqlplus uname/pwd@dbname <<eof! set verify off set heading off set feedback off select bill_period from bill_period_ref where... (4 Replies)
Discussion started by: ss_ss
4 Replies

8. UNIX and Linux Applications

SQL Lite query

Hello Everyone, I am looking to write a script that will run on many machines in a network at the same time. They need to write a result to a common location. I plan to use a SQLlite database as this common writing point. But the concern I have is how SQLlite will react to multiple writes that... (1 Reply)
Discussion started by: garric
1 Replies

9. Shell Programming and Scripting

rsh and sql query

Hi ... I am doing a switch user and then rsh and then running a sql query . I am successfull in rsh and logging into the database , but my query doesnt run .. Here's the command : su - linus -c "rsh -l linus psmf ORACLE_SID=SMP;export ORACLE_SID;sqlplus... (1 Reply)
Discussion started by: sars
1 Replies

10. Shell Programming and Scripting

& in SQL query

I have a script that looks for all jobs that contain a particular calendar. Some of the calendars have '&' in them and sql freaks out when it encounters that.. is there a way around this? I have tried: select job_name from job where run_calendar='1&15dom' select job_name from job... (3 Replies)
Discussion started by: Lindarella
3 Replies
Login or Register to Ask a Question