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

I see a presentation of data, but I don't see a question. What is it that the shell script suppose to do??

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

Ask Your Question

Weekly Poll