Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Parse SQL text and only format first SELECT statement.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Parse SQL text and only format first SELECT statement.
# 1  
Old 08-30-2013
Parse SQL text and only format first SELECT statement.
Hi Forum.

Need your expertise on the following question.

I have the following file which I would like to parse, find first block of SELECT statment and concatenate all input fields as 1 field (~ delimited):

Code:
Old File:
SELECT /*+ USE_HASH(CCOMM ICAR IMAP IAS IP IMAS IMPS IAP SPCA) */
        CCOMM.CAMPAIGN_SK AS CAMPAIGN_SK,
         CCOMM.CONTACT_DTTM AS ENTRY_DATE,
         '1005' AS COST_CENTRE,
         SCPA.CIF AS CIF
    FROM (  SELECT /*+ USE_HASH(CCP CCCH CC CCE CCOMM CCOMME) */
                  MAX (CCP.CAMPAIGN_SK) AS CAMPAIGN_SK,
                   CCCH.CUSTOMER_KEY AS CUSTOMER_KEY,
                   MAX (TRUNC (CCCH.CONTACT_DTTM)) AS CONTACT_DTTM,
                   MAX (CCOMME.OFFER) AS OFFER,
                   MIN (CCE.CAMPAIGN_START_DATE) AS CAMPAIGN_START_DATE,
                   MAX (CCE.CAMPAIGN_END_DATE) AS CAMPAIGN_END_DATE
              FROM SAS_MA.CI_CELL_PACKAGE CCP,
                   SAS_MA.CI_CUST_CONTACT_HISTORY CCCH,
                   SAS_MA.CI_CAMPAIGN CC,
                   SAS_MA.CI_CAMPAIGN_EXT CCE,
                   SAS_MA.CI_COMMUNICATION CCOMM,
                   SAS_MA.CI_COMMUNICATION_EXT CCOMME
                   .....

Code:
Desired File:
SELECT /*+ USE_HASH(CCOMM ICAR IMAP IAS IP IMAS IMPS IAP SPCA) */
CCOMM.CAMPAIGN_SK||'~'||CCOMM.CONTACT_DTTM||'~'||'1005'||'~'||SCPA.CIF
    FROM (  SELECT /*+ USE_HASH(CCP CCCH CC CCE CCOMM CCOMME) */
                  MAX (CCP.CAMPAIGN_SK) AS CAMPAIGN_SK,
                   CCCH.CUSTOMER_KEY AS CUSTOMER_KEY,
                   MAX (TRUNC (CCCH.CONTACT_DTTM)) AS CONTACT_DTTM,
                   MAX (CCOMME.OFFER) AS OFFER,
                   MIN (CCE.CAMPAIGN_START_DATE) AS CAMPAIGN_START_DATE,
                   MAX (CCE.CAMPAIGN_END_DATE) AS CAMPAIGN_END_DATE
              FROM SAS_MA.CI_CELL_PACKAGE CCP,
                   SAS_MA.CI_CUST_CONTACT_HISTORY CCCH,
                   SAS_MA.CI_CAMPAIGN CC,
                   SAS_MA.CI_CAMPAIGN_EXT CCE,
                   SAS_MA.CI_COMMUNICATION CCOMM,
                   SAS_MA.CI_COMMUNICATION_EXT CCOMME
                   .....

Thank you for all feedback/insight that you may have.
# 2  
Old 08-30-2013
I'm not sure if this is general enough for your real input, but the following awk script works for your input sample:
Code:
awk -v sq="'" '
copy {  print
        next
}       
found && $2 == "AS" {
        printf("%s%s", pc++ ? "||"sq"~"sq"||" : "", $1)
        next
}       
found && $1 == "FROM" {
        printf("\n%s\n", $0)
        copy = 1
        next
}
$1 == "SELECT" {
        found = 1
        print
}' Old

If you're using a Solaris/SunOS system, use /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk instead of awk.
This User Gave Thanks to Don Cragun For This Post:
pchang
# 3  
Old 09-04-2013
Thank you Don - I finally had a chance to test the awk script that you provided and it's working as expected.

How can I embed the code into an awk script?
# 4  
Old 09-04-2013
Quote:
Originally Posted by pchang
Thank you Don - I finally had a chance to test the awk script that you provided and it's working as expected.

How can I embed the code into an awk script?
I don't understand the question. Why do you want to embed an awk script in an awk script?
# 5  
Old 09-04-2013
Actually, what I meant was I wanted to embed the awk code into a shell script so I can just call the shell script and pass the file as the first parameter.

Thank you - I have it working now as a shell script Smilie
Last edited by pchang; 09-04-2013 at 04:18 PM.. Reason: added more comments
# 6  
Old 09-04-2013
Here is what you requested. If you want to feed the output produced by this script into your database SQL interface as part of the script, you'll have to show us the SQL commands you use.

I use the Korn shell, but this will work with any shell that recognizes basic POSIX conformant shell variable expansions and syntax.
Code:
#!/bin/ksh
if [ $# -ne 1 ]
then   printf "Usage: %s filename\n" "${0##*/} >&2
        exit 1
fi
awk -v sq="'" '
copy {  print
        next
}       
found && $2 == "AS" {
        printf("%s%s", pc++ ? "||"sq"~"sq"||" : "", $1)
        next
}       
found && $1 == "FROM" {
        printf("\n%s\n", $0)
        copy = 1
        next
}
$1 == "SELECT" {
        found = 1
        print
}' "$1"

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SQLPLUS command with more than 1 select statement

Hi all, I'm using below code processId=`sqlplus -s ${sysuser}/${syspwd} <<CHK_PROCESS whenever sqlerror exit sql.sqlcode; set head off feedback off echo off pages 0 SELECT PROCESS_ID FROM LSHADMIN.DATA_DOMAIN WHERE DOMAIN_NAME = '${tabname}' ... (8 Replies)
Discussion started by: Pratiksha Mehra
8 Replies

2. Shell Programming and Scripting

Help with awk statement to format text file.

Hello, I am fairly new to shellscripting and have written a script to check on messages file and report failed logins: Here is the original file: Jul 17 03:38:07 sfldmilx086 sshd: error: PAM: Authentication failure for houghn97 from 10.135.77.201 Jul 17 03:38:07 sfldmilx086 sshd: error:... (2 Replies)
Discussion started by: neilh1704
2 Replies

3. Shell Programming and Scripting

Problem with select statement

Hi I have run out of ideas as to why this select doesn't work in a script I am writing. The script sources a file of common functions and I am trying to use a select statement within one of the functions - PS3="Try? " select X in CONT EXIT; do if ] ... (4 Replies)
Discussion started by: steadyonabix
4 Replies

4. Shell Programming and Scripting

script to parse text file into sql commands

Hello all, I tried searching for something similiar before posting but couldn't come up with anything that fit what I need. In Linux, I'm trying to parse through a number of files and take the info in them and put it into mysql. My file is a dump from an expect script: ---filename... (3 Replies)
Discussion started by: hamanjam
3 Replies

5. Shell Programming and Scripting

for each value in an array, execute select statement

Hello All, I am new to shell scripting. I am working on Solaris O/S, bash script and sybase programming. I want to loop through multiple values in an array and for each value, I want to select a row from the database. following is the code written for it. output="loop.csv" ... (8 Replies)
Discussion started by: arundhati_s
8 Replies

6. Shell Programming and Scripting

Select variable within a if statement

i want to select a variable created and use it in a if statement, but not getting the desired results LINE='device for 0101a01: lpd://172.25.41.111:515' prt=`echo $LINE | awk '{print $3 }' | cut -c 1-7` echo $prt My if statement to select just what i want.. IFS=$":" while read prt... (11 Replies)
Discussion started by: ggoliath
11 Replies

7. Shell Programming and Scripting

How can i assign an select statement into a variable?

I am trying to assign an select statement into a variable. Can someone hel me with this. example : a='select * from dual' echo $a should give me select * from dual But this is not working. I trying with \ before * and quotes too. (1 Reply)
Discussion started by: rdhanek
1 Replies

8. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

9. UNIX and Linux Applications

Oracle Select IN statement

If I recall, when I used informix I could do a sql statement like: SELECT Value from Table WHERE ID in (100,200,300); How do I do this in Oracle? I believe I am using Oracle 10 if that matters. Thanks. (1 Reply)
Discussion started by: benefactr
1 Replies

10. UNIX for Dummies Questions & Answers

Pipe SQL select statement results to script

Hello I would like to perform a select from a oracle table and return those values to my shell script For example: site=head -1 $infile | cut -c1-15 | awk '{printf "s%", $0} sqlplus -s /nolog |& #Open pipe to sql select col1, col2, col3, col4 from oracle_table where col5 =... (6 Replies)
Discussion started by: houtakker
6 Replies
Login or Register to Ask a Question