Parse SQL text and only format first SELECT statement. Post: 302849017

Sponsored Content

Top Forums Shell Programming and Scripting Parse SQL text and only format first SELECT statement. Post 302849017 by pchang on Friday 30th of August 2013 03:55:18 PM

08-30-2013

Registered User

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.

pchang

View Public Profile for pchang

Find all posts by pchang

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

10. 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}' ...

LEARN ABOUT NETBSD

param

PARAM(3)						   BSD Library Functions Manual 						  PARAM(3)

NAME

     param -- common parameters

SYNOPSIS

     #include <sys/param.h>

     size
     MAX(size a, size b);

     size
     MIN(size a, size b);

DESCRIPTION

     The <sys/param.h> header includes some common definitions and macros specific to NetBSD.  The header is perhaps best characterized as a ker-
     nel equivalent of <sys/types.h>.  The following list summarizes the provided definitions and macros.

	   o   First and foremost, the header defines the version of NetBSD.  This is defined as

		     #define __NetBSD_Version__    599004800   /* 5.99.48 */

	       The general format is ``MMmmrrpp00'', where 'MM' and 'mm' denote the major and minor version, respectively, 'rr' is provided for
	       compatibility, and 'pp' defines the patch level.

	   o   Common utility macros such as MAX() and MIN() as well as more specific macros such as STACK(9), ctod(9), mstohz(9), roundup(9), and
	       setbit(9).

	   o   Numerous miscellaneous definitions such as limits, constants for the kernel memoryallocators(9), scale factors used by the sched-
	       uler, kthread(9) priorities, and many others.

	   o   Definitions provided for historical and compatibility reasons.  Examples range from definitions such as ``#define BSD'' to old pri-
	       ority levels used in the kernel.

SEE ALSO

     bitops(3), cdefs(3), types(3), unistd(3)

HISTORY

     A <param.h> header appeared already in the Version 4 AT&T UNIX.

BSD
								  April 10, 2011							       BSD

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Pipe SQL select statement results to script

Discussion started by: houtakker

2. UNIX and Linux Applications

Oracle Select IN statement

Discussion started by: benefactr

3. Shell Programming and Scripting

using SELECT sql statement in shell script

Discussion started by: hcclnoodles

4. Shell Programming and Scripting

How can i assign an select statement into a variable?

Discussion started by: rdhanek