How to convert data tree to SQL in UNIX shell scripting

July 12, 2015 at 23:41:53
Specs: UNIX KSH
Table TAB1 contains following example data (its a tree sitting in table data format & its drived based CHILD & PARENT column pick the RULE condition to generate the below SQL:

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:

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)

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

we are new to UNIX shell script so dont know how to scipt it, any help is much appriciated, thanks


See More: How to convert data tree to SQL in UNIX shell scripting

Report •

#1
July 13, 2015 at 08:52:54
I see a presentation of data, but I don't see a question. What is it that the shell script suppose to do??

Report •

#2
July 13, 2015 at 11:04:20
I need to write a shell script to read the above data & write to SQL (for provided data to generate provided SQL), since I am new to Shell, dont know the start point to do.. giving sample SQL will help, thanks

Report •
Related Solutions


Ask Question