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

Do you think facial recognition technology should be regulated?

Discuss in The Lounge

Poll History