Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to create SQL statement out of data using shell script?
Top Forums Shell Programming and Scripting How to create SQL statement out of data using shell script? Post 302949539 by gksenthilkumar on Monday 13th of July 2015 03:00:25 AM
Old 07-13-2015
How to create SQL statement out of data using shell script?
Table TAB1 contains following example data (its a tree sitting in table data format & its driven based CHILD & PARENT column pick the RULE condition to generate the below SQL:

Code:
CHILD	PARENT	SS	MID	MNM	VNM	RULE	FLG
1	?	S1	?	?	V1	rule004	I         
2	1	S1	?	?	V1	0	Z         
3	1	S1	?	?	V1	1	Z         
4	2	S1	?	?	V2	rule001	N         
5	4	S1	?	?	V2	-999999999 to 20000 OR Missing	Z         
6	4	S1	?	?	V2	20000.000001 to 999999999	Z         
10	5	S1	?	?	V3	rule002	N         
11	10	S1	?	?	V3	360 to 700 OR Missing	Z         
12	10	S1	?	?	V3	700.000001 to 850	Z         
13	6	S1	?	?	V3	rule002	N         
14	13	S1	?	?	V3	360 to 700 OR Missing	Z         
15	13	S1	?	?	V3	700.000001 to 850	Z         
16	3	S1	60054	M5	?	?	Y         
17	11	S1	60050	M1	?	?	Y         
18	12	S1	60051	M2	?	?	Y         
19	14	S1	60052	M3	?	?	Y         
20	15	S1	60053	M4	?	?	Y         
99999999	99999999	S1	60050	M1	xxxxxxxx	?	D

the output SQL looks like:

Code:
SELECT ROW_KEY_COL,
CASE  
WHEN V1  = 1 THEN 'M5'  
WHEN V1  = 0 AND (V2 BETWEEN -999999999 AND 20000   OR V2 IS NULL ) AND (V3 BETWEEN 360 AND 700   OR V3 IS NULL ) THEN 'M1'  
WHEN V1  = 0 AND (V2 BETWEEN -999999999 AND 20000   OR V2 IS NULL )  AND V3 BETWEEN 700.000001 AND 850 THEN 'M2'  
WHEN V1  = 0 AND V2 BETWEEN 20000.000001 AND 999999999 AND (V3 BETWEEN 360 AND 700   OR V3 IS NULL ) THEN 'M3'  
WHEN V1  = 0 AND V2 BETWEEN 20000.000001 AND 999999999 AND V3 BETWEEN 700.000001 AND 850 THEN 'M4' 
ELSE 'M1' END
FROM TAB1;

(OR)

Code:
SELECT ROW_KEY_COL,
CASE  
WHEN V1  = 0 AND (V2 BETWEEN -999999999 AND 20000   OR V2 IS NULL ) AND (V3 BETWEEN 360 AND 700   OR V3 IS NULL ) THEN 'M1'  
WHEN V1  = 0 AND (V2 BETWEEN -999999999 AND 20000   OR V2 IS NULL )  AND V3 BETWEEN 700.000001 AND 850 THEN 'M2'  
WHEN V1  = 0 AND V2 BETWEEN 20000.000001 AND 999999999 AND (V3 BETWEEN 360 AND 700   OR V3 IS NULL ) THEN 'M3'  
WHEN V1  = 0 AND V2 BETWEEN 20000.000001 AND 999999999 AND V3 BETWEEN 700.000001 AND 850 THEN 'M4' 
WHEN V1  = 1 THEN 'M5'  
ELSE 'M1' END
FROM TAB1;

its kind of based on 1st rule it goes to 2nd rule then 3 rule & each rule contains 2 or more rule branch & it goes to until # of sub tree levels & it generate kind of above SQL query..

any help/suggestion will help, thanks

---------- Post updated at 03:00 AM ---------- Previous update was at 02:54 AM ----------

another example data & output SQL looks like below:

Code:
CHILD	PARENT	SS	MID	MNM	VNM	RULE	FLG
1	?	S2	?	?	V1	rule002	I         
2	1	S2	?	?	V1	N	Z         
3	1	S2	?	?	V1	Y	Z         
4	2	S2	?	?	V2	rule003	N         
5	4	S2	?	?	V2	'AIRLINE','HOTEL'	Z         
6	4	S2	?	?	V2	CORPORATE	Z         
7	5	S2	12331	M1	?	?	Y         
8	6	S2	12332	M2	?	?	Y         
9	3	S2	12332	M3	?	?	Y         
99999999	99999999	S2	12333	M4	XXXXXXXX	?	D

output SQL needs following format is:

Code:
SELECT ROW_KEY_COL,
CASE  
WHEN V1  IN ('airline','hotel') AND V2  = 'N' THEN 'M1'  
WHEN V1  = 'corporate' AND V2  = 'N' THEN 'M2'  
WHEN V2  = 'Y' THEN 'M3' 
ELSE 'M4' END
FROM TAB1

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

how to assign sql output data to shell script variable

Hi Guys ! I am new to unix and want to find out how we can make sql statement data to shell script variable? Any help/suggestion is greatly appreciated -Chandra (1 Reply)
Discussion started by: kattics
1 Replies

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

3. Shell Programming and Scripting

How to pass tablenames from a file to shell script to execute create statement in DB2

Hi, I am new to Shell Scripting, and I need to create nicknames for 600 tables in db2. I have the file names in a text file and i have to pass these table names to a shell script create nicknames in db2. Can some one please help me in this regard. (1 Reply)
Discussion started by: kamalanaatha
1 Replies

4. Shell Programming and Scripting

How to get full sql table data using shell script

i have a Oracle table like col1 col2 ---- ----- a 1 b 2 c 3 when i write a script for it , it gives me all the data in one column. Please give me the solution which gives the exact result like we see in sql editors. for a in `echo " set feedback off; set pagesize 40;... (1 Reply)
Discussion started by: ss135r
1 Replies

5. Programming

create a spool file based on values passed from korn shell to sql script

this is my issue. 4 parameters are passed from korn shell to sql script. parameter_1= varchar2 datatype or no value entered my user. parameter_2= number datatype or no value entered my user. parameter_3= number datatype or no value entered my user. parameter_4= number datatype or no... (5 Replies)
Discussion started by: megha2525
5 Replies

6. Shell Programming and Scripting

Korn shell script - SQL statement challenges

Hi scripting experts. I have some coding challenges that I'm hoping you can help me out. I have one file#1 that contains the following sql statement that spans over multiple lines: sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id... (1 Reply)
Discussion started by: pchang
1 Replies

7. Homework & Coursework Questions

Write a shell script for SQL loader to load data into a staging table

Hi, I'm new to Linux. I'm working on a database, and need to load data in a database table (which I already created) using shell script. The table has two columns - Acct_number (not nullable) and date (timestamp). I'm not able to write a shell script for that. Can any one help me? ... (3 Replies)
Discussion started by: saisudeep
3 Replies

8. Shell Programming and Scripting

Generate sql statement using shell scripting

Can anyone please assist me? I have attached 2 input files and one output file. I need to generate the sql update statements using the above 2 input files. if inputfile2 has 5 rows, then we should generate 5 update statements because column1 is unique. inputfile1 and inputfile2 may contain more... (10 Replies)
Discussion started by: vinus
10 Replies

9. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET... (8 Replies)
Discussion started by: Jaganjag
8 Replies

10. Shell Programming and Scripting

Oop to copy and rename files through SQL Statement in shell Script

#!/bin/sh sqlplus -s "/ as sysdba" << EOF SET HEADING OFF SET FEEDBACK OFF Select pt.user_concurrent_program_name , OUTFILE_NAME FROm apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests f where pt.concurrent_program_id = f.concurrent_program_id and pt.application_id =... (1 Reply)
Discussion started by: usman_oracle
1 Replies
All times are GMT -4. The time now is 06:23 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy