#!/bin/sh
awk -F'\t' '
BEGIN { printf "SELECT ROW_KEY_COL,\nCASE\n" }
{sub("[ \r]+$","")}
$8 ~ "[ZN]" {
if($8=="Z") {
RL[$1]=$7
if ($7 != "All Other")
{ if ( $7 ~ /[0-9]+$/ ) Val = $7;
else Val=sprintf("%c%s%c",39,$7,39);
if( AOTH[$6 $2] == "") AOTH[$6 $2]= Val;
else AOTH[$6 $2]= AOTH[$6 $2] "," Val;}
}
VAR[$1]=$6
NXT[$1]=$2
#print $6 "==" AOTH[$6] "\n"
next
}
$8 == "D" { printf "ELSE %c%s%c END\n",39,$5,39}
$8=="Y" {
wexp=sprintf("THEN %c%s%c\n",39,$5,39)
rule=$2
ND=" "
while(rule in NXT) {
num=split(RL[rule],rf," ")
#print RL[rule] " = " num;
if (num==1) {
if (index(RL[rule], ",")) wexp=sprintf("%s IN (%s)%s",VAR[rule],RL[rule],ND) wexp
else
if (RL[rule]+0 > 0 || RL[rule]+0 == RL[rule]) wexp=sprintf("%s = %s%s",VAR[rule],RL[rule],ND) wexp
else wexp=sprintf("%s = %c%s%c%s",VAR[rule],39,RL[rule],39,ND) wexp
}
if (num==3) wexp=sprintf("%s BETWEEN %s AND %s%s",VAR[rule],rf[1],rf[3],ND) wexp
if (num==5) wexp=sprintf("(%s BETWEEN %s AND %s OR %s IS NULL)%s",VAR[rule],rf[1],rf[3],VAR[rule],ND) wexp
if (RL[rule]=="All Other") wexp=sprintf("(%s NOT IN (%s) )%s",VAR[rule],AOTH[VAR[rule] NXT[rule]],ND) wexp
# parse done
rule=NXT[rule]
ND = " AND "
}
printf "WHEN %s", wexp
}
END { printf "FROM TAB1;\n" }' infile
INPUT DATA
----------
Code:
CHILD PARENT SS MID MNM VNM RULE FLG
1 S1 V1 rule001 I
2 1 S1 V1 high_bureau Z
3 1 S1 V1 All Other Z
4 2 S1 V2 rule002 N
5 4 S1 V2 Charge V2 Z
6 4 S1 V2 All Other Z
7 3 S1 V2 rule002 N
8 7 S1 V2 Charge V2 Z
9 7 S1 V2 All Other Z
10 5 S1 V3 rule003 N
11 10 S1 V3 1 to 1 Z
12 10 S1 V3 2 to 2 Z
13 10 S1 V3 3 to 3 Z
14 10 S1 V3 4 to 4 Z
15 10 S1 V3 5 to 5 Z
16 10 S1 V3 6 to 6 Z
17 10 S1 V3 7 to 7 Z
18 6 S1 V3 rule003 N
19 18 S1 V3 1 to 1 Z
20 18 S1 V3 2 to 2 Z
21 18 S1 V3 3 to 3 Z
22 18 S1 V3 4 to 4 Z
23 18 S1 V3 5 to 5 Z
24 18 S1 V3 6 to 6 Z
25 18 S1 V3 7 to 7 Z
26 8 S1 V3 rule004 N
27 26 S1 V3 1 to 1 Z
28 26 S1 V3 2 to 2 Z
29 26 S1 V3 3 to 4 Z
30 26 S1 V3 5 to 7 Z
31 9 S1 V3 rule004 N
32 31 S1 V3 1 to 1 Z
33 31 S1 V3 2 to 2 Z
34 31 S1 V3 3 to 4 Z
35 31 S1 V3 5 to 7 Z
36 12 S1 65264 SS519 Y
37 20 S1 65264 SS519 Y
38 28 S1 65264 SS519 Y
39 33 S1 65264 SS519 Y
40 11 S1 65244 SS501 Y
41 13 S1 65245 SS502 Y
42 14 S1 65246 SS503 Y
43 15 S1 65247 SS504 Y
44 16 S1 65248 SS505 Y
45 17 S1 65249 SS506 Y
46 19 S1 65251 SS507 Y
47 21 S1 65252 SS508 Y
48 22 S1 65253 SS509 Y
49 23 S1 65254 SS510 Y
50 24 S1 65255 SS511 Y
51 25 S1 65256 SS512 Y
52 27 S1 65258 SS513 Y
53 29 S1 65259 SS514 Y
54 30 S1 65260 SS515 Y
55 32 S1 65261 SS516 Y
56 34 S1 65262 SS517 Y
57 35 S1 65263 SS518 Y
99999999 99999999 S1 65264 SS519 XXXXXXXX D
SHELL OUTPUT (shell script is producing below output)
----------------------------------------------------
Code:
SELECT ROW_KEY_COL,
CASE
WHEN V1 = 'high_bureau' AND V3 BETWEEN 1 AND 1 THEN 'SS501'
WHEN V1 = 'high_bureau' AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN V1 = 'high_bureau' AND V3 BETWEEN 3 AND 3 THEN 'SS502'
WHEN V1 = 'high_bureau' AND V3 BETWEEN 4 AND 4 THEN 'SS503'
WHEN V1 = 'high_bureau' AND V3 BETWEEN 5 AND 5 THEN 'SS504'
WHEN V1 = 'high_bureau' AND V3 BETWEEN 6 AND 6 THEN 'SS505'
WHEN V1 = 'high_bureau' AND V3 BETWEEN 7 AND 7 THEN 'SS506'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 1 AND 1 THEN 'SS507'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 3 AND 3 THEN 'SS508'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 4 AND 4 THEN 'SS509'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 5 AND 5 THEN 'SS510'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 6 AND 6 THEN 'SS511'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 7 AND 7 THEN 'SS512'
WHEN (V1 NOT IN ('high_bureau') ) AND V3 BETWEEN 1 AND 1 THEN 'SS513'
WHEN (V1 NOT IN ('high_bureau') ) AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN (V1 NOT IN ('high_bureau') ) AND V3 BETWEEN 3 AND 4 THEN 'SS514'
WHEN (V1 NOT IN ('high_bureau') ) AND V3 BETWEEN 5 AND 7 THEN 'SS515'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 1 AND 1 THEN 'SS516'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 3 AND 4 THEN 'SS517'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 5 AND 7 THEN 'SS518'
ELSE 'SS519' END
FROM TAB1;
EXPECTED OUPUT
-----------------
Code:
SELECT ROW_KEY_COL,
CASE
WHEN V1 = 'high_bureau' AND V2 = 'Charge V2' AND V3 BETWEEN 1 AND 1 THEN 'SS501'
WHEN V1 = 'high_bureau' AND V2 = 'Charge V2' AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN V1 = 'high_bureau' AND V2 = 'Charge V2' AND V3 BETWEEN 3 AND 3 THEN 'SS502'
WHEN V1 = 'high_bureau' AND V2 = 'Charge V2' AND V3 BETWEEN 4 AND 4 THEN 'SS503'
WHEN V1 = 'high_bureau' AND V2 = 'Charge V2' AND V3 BETWEEN 5 AND 5 THEN 'SS504'
WHEN V1 = 'high_bureau' AND V2 = 'Charge V2' AND V3 BETWEEN 6 AND 6 THEN 'SS505'
WHEN V1 = 'high_bureau' AND V2 = 'Charge V2' AND V3 BETWEEN 7 AND 7 THEN 'SS506'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 1 AND 1 THEN 'SS507'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 3 AND 3 THEN 'SS508'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 4 AND 4 THEN 'SS509'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 5 AND 5 THEN 'SS510'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 6 AND 6 THEN 'SS511'
WHEN V1 = 'high_bureau' AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 7 AND 7 THEN 'SS512'
WHEN (V1 NOT IN ('high_bureau') ) AND V2 = 'Charge V2' AND V3 BETWEEN 1 AND 1 THEN 'SS513'
WHEN (V1 NOT IN ('high_bureau') ) AND V2 = 'Charge V2' AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN (V1 NOT IN ('high_bureau') ) AND V2 = 'Charge V2' AND V3 BETWEEN 3 AND 4 THEN 'SS514'
WHEN (V1 NOT IN ('high_bureau') ) AND V2 = 'Charge V2' AND V3 BETWEEN 5 AND 7 THEN 'SS515'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 1 AND 1 THEN 'SS516'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 2 AND 2 THEN 'SS519'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 3 AND 4 THEN 'SS517'
WHEN (V1 NOT IN ('high_bureau') ) AND (V2 NOT IN ('Charge V2') ) AND V3 BETWEEN 5 AND 7 THEN 'SS518'
ELSE 'SS519' END
FROM TAB1;
The issue is:-
If the RULE column value contains NON-NUMERIC value and if it contains SPACE between word then its causing that issue.. (Charge V2 --> if change to --> Charge_v2 then its working fine)
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)
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)
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)
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)
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)
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)
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)
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)
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)
#!/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)
10-26-2015