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):

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
                   ..... 

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.

I'm not sure if this is general enough for your real input, but the following awk script works for your input sample:

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 .

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?

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

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.

#!/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"