Shell to SQL for data


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
Shell to SQL for data

Input:

HTML Code:
Table: TAB1							
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
need below output for above input data:

Code:
select 'S1' as SS,cast((case  when V1  = 1 then 'M5'  when (V3 between 360 and 700   OR V3 is null )  and (V2 between -999999999 and 20000   OR V2 is null )  and V1  = 0 then 'M1'  when V3 between 700.000001 and 850 and (V2 between -999999999 and 20000   OR V2 is null )  and V1  = 0 then 'M2'  when (V3 between 360 and 700   OR V3 is null )  and V2 between 20000.000001 and 999999999 and V1  = 0 then 'M3'  when V3 between 700.000001 and 850 and V2 between 20000.000001 and 999999999 and V1  = 0 then 'M4' else 'M1' end) as varchar(100)) as MNM from TAB1;

help to provide shell script to generate output SQL for input data ?, thx!
# 2  
What have you tried so far?

What output/errors do you get?

Please show attempts at a solution and specify what OS and versions are being used.
# 3  
we have written in Teradata programming language to generate this sql, now plan to put in Unix, so don’t know how to do, if we have some sample for this help to enhance further
# 4  
tried below & not producing expected.. any help?, thanks


the core part is below half..


Code:
#!/bin/ksh
#set -x
#ksh shell_to_sql.ksh -d db1 -f 2015094 -o tera1 -l use_act -r run -i use_act_tbl -k unique_key_col -g 0
##----------------------------------------------------##
# Constants and Variables
##----------------------------------------------------##
export SCRIPT_NAME_FULL=$(basename $0)
export SCRIPT_NAME=${SCRIPT_NAME_FULL%.*}
export USAGE_STRING=`cat <<-EOF
Usage: ${SCRIPT_NAME} 
Required:
-o TD_ODBC_DSN        <Teradata DSN>
-d TD1_DATABASE       <Teradata Database>
-i INPUT_TBL     <Input Derivation Table>
-f F_KEY              <yyyyddd date>
-r RUN_TYPE           <Run type>
-k KEY_COL            <Key Column>
-l user_act_NM         <user act Name>
-g DEBUG_FLG          <Debug Flag>
EOF`

#Set Defaults for all variables
export USER=$(whoami) 
export PWD=$HOME
export DELIMITER='|' 

# Get the options from the command line
while getopts d:f:o:l:r:i:k:g OPTION ; do
    case ${OPTION} in
        d) export TD1_DATABASE=${OPTARG};;
        f) export F_KEY=${OPTARG};;
        o) export TD_ODBC_DSN=${OPTARG};;
        l) export user_act_nm=${OPTARG};;
        r) export RUN_TYPE=${OPTARG};;
        i) export INPUT_TBL=${OPTARG};;
        k) export KEY_COL=${OPTARG};;
        g) export DEBUG_FLG=1;;
    esac
done

# Load the TD ODBC entry
DSN_EXPORT_STATEMENTS=`$HOME/odbc_dsn.ksh ${TD_ODBC_DSN} tera1`
${DSN_EXPORT_STATEMENTS}

#Extracting user_act_nms
echo $user_act_nm>in_usecase_NMs.out


# Set temp files used for fixed and delim determination
export FILE_FIXDEL="$PWD/file.log"
export FILE_FIXDEL_OUT3="$PWD/var_check.out"
export FILE_FIXDEL_OUT4="$PWD/user_act_nmdata.out"
export FILE_FIXDEL_OUT11="$PWD/column_type_check.out"
export FILE_FIXDEL_OUT9="$PWD/vt_varnm_lst.out"
export FILE_FIXDEL_OUT14="$PWD/act_cd.out"
export FILE_FIXDEL_OUT01="$PWD/session1.out"

#Execute SQL to determine run_type availability in t_act_cd table.
echo ".export DATA file = "${FILE_FIXDEL_OUT3}";
.SET RECORDMODE OFF;
SELECT 1 FROM mpt_tbl WHERE user_act_nm = '"$user_act_nm"' AND var_nm<>'XXXXXXXX' AND var_nm IS NOT NULL  AND TRIM(var_nm)<>'' AND end_dt='9999-12-31' AND var_nm NOT IN (SELECT columnname FROM dbc.COLUMNS WHERE tablename='"$INPUT_TBL"' AND databasename=(SELECT DATABASE));
.export reset;
.export DATA file = "${FILE_FIXDEL_OUT4}";
.SET RECORDMODE OFF;
SELECT usecase_child,usecase_parent,user_act_nm,musecase_id,musecase_nm,var_nm,cma_rule_cond,release_flg,end_flg,start_dt,end_dt FROM mpt_tbl WHERE user_act_nm = '"$user_act_nm"';
.export reset;
.export DATA file = "${FILE_FIXDEL_OUT11}";
.SET RECORDMODE OFF;
select type ("$INPUT_TBL"."$KEY_COL");
.export reset;
.export DATA file = "${FILE_FIXDEL_OUT14}";
.SET RECORDMODE OFF;
select act_cd from t_act_cd where act_desc='"$run_typ_1"';
.export reset;">>mpt_tbl.out

bteq >> ${FILE_FIXDEL} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/mpt_tbl.out;
.export reset;
.export DATA file = '${FILE_FIXDEL_OUT01}';
.SET RECORDMODE OFF;
select session;
.export reset;
.quit;
EOF

if [ "x"$li_error_cd = "x" ]; then #outer error check
li_mdl_cnt_alloth=0
#awk -F '|' ' { print $1 }' usecase_master.out>usecasenm_list.out
#awk '{a[$1]++}a[$1]<2' usecasenm_list.out>usecasenm_list1.out
#sed 's/ //g' user_act_nmdata.out >user_act_nmdata11.out
awk -F '|' '$12 = "9999-12-31" {print $3}' user_act_nmdata.out>user_act_nmdata1.out
#awk -F '|' ' { print $1 }' user_act_nmdata1.out>user_act_nmdata2.out

#sort IP_usecase_NMs.out> IP_usecase_NMss.sorted
sort user_act_nmdata1.out> user_act_nmdata1.sorted
comm -23 in_usecase_NMs.out user_act_nmdata1.sorted >usecase_check.out

    if [ $(wc -l < usecase_check.out) -ge 1 ]; then
       echo "usecaseATION EXISTANCE CHECK PASSED"

        #variable existence check
        if [ $(wc -l < var_check.out) -ge 1 ]; then
           export li_error_cd=59;
           if [ $li_debug_flg = "1" ]; then
              echo ""
              echo "Error !! Column not found in dbc.columns.."
           fi
        fi
# Log
if [ $li_debug_flg = "1" ]; then
   echo "****`date` - Step 3    : Variable list preparation.."
fi
        if [ "x"$li_error_cd = "x" ]; then

export FILE_FIXDEL9="$PWD/file9.log"
export FILE_FIXDEL_OUT9="$PWD/vt_varnm_lst.out"
echo ".export DATA file = "${FILE_FIXDEL_OUT9}";
.SET RECORDMODE OFF;
 SELECT var_nm FROM mpt_tbl WHERE  user_act_nm = '"$user_act_nm"' AND var_nm<>'XXXXXXXX' AND var_nm IS NOT NULL AND end_dt='9999-12-31' AND TRIM(var_nm)<>'' GROUP BY var_nm;
.export reset;">>vt_varnm_lstt.out

bteq >> ${FILE_FIXDEL9} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.export DATA file = '${FILE_FIXDEL_OUT9}';
.RUN FILE = $HOME/vt_varnm_lstt.out;
.export reset;
.quit;
EOF

            paste -d, -s vt_varnm_lst.out >lv_var_qry.out
            lv_var_qry=$(<lv_var_qry.out)
            
        fi

      echo "create table vt_ip_usecase_tbl as (select "$KEY_COL","$lv_var_qry" from "$INPUT_TBL" where feed_key="$F_KEY") with data;">>vt_ip_usecase_tbl.out

          tr '[:upper:]' '[:lower:]' < in_usecase_NMs.out > IP_usecase_NMs1.out
          if [ "x"$li_error_cd = "x" ]; then #outer error code check

              if [ "x"$li_error_cd = "x" ]; then
    
export FILE_FIXDEL10="$PWD/file10.log"
#export FILE_FIXDEL_OUT15="$PWD/dec_ds1.out" 
export FILE_FIXDEL_OUT10="$PWD/tmp_msm.out" 

bteq >> ${FILE_FIXDEL10} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
DEL FROM tmp_msm WHERE user_act_nm = '$user_act_nm';
DEL FROM tmp_msm WHERE session_id in (select session);
.export DATA file = '${FILE_FIXDEL_OUT10}';
.SET RECORDMODE OFF;
INSERT INTO tmp_msm(usecase_child, usecase_parent, user_act_nm, musecase_nm, var_nm,cma_rule_cond, end_flg) SELECT usecase_child, usecase_parent, user_act_nm, musecase_nm, var_nm, LOWER(cma_rule_cond), end_flg FROM mpt_tbl WHERE end_dt='9999-12-31' AND user_act_nm = '$user_act_nm';
update tmp_msm set session_id=(sel session) WHERE user_act_nm = '$user_act_nm';
select session_id,usecase_child, usecase_parent, user_act_nm, musecase_nm, var_nm,cma_rule_cond, end_flg from tmp_msm where user_act_nm='$user_act_nm';
.export reset;
.quit;
EOF
           awk -F '|' ' { print $1 }' tmp_msm.out>session.out
           export session=`awk '{print }' session.out | sort | uniq`

           if [ $(wc -l < tmp_msm.out) -ge 1 ]; then
           echo ""
           else
           li_error_cd=51
           fi

           if [ "x"$li_error_cd = "x" ]; then #inner error code check

               # Log
               if [ $li_debug_flg = "1" ]; then
                  echo "****`date` - Step 4 : creating table vt__usecase_tbl.."
               fi
             export CHAR_CHECK=`cat $FILE_FIXDEL_OUT11|grep CHAR|wc -l|sed -e 's/^ *//'|sed -e 's/ *$//'`
            if [ $CHAR_CHECK -eq 1 ]; then
              export lv_key_col='0$KEY_COL'
			   export lv_col_nm='key_col_chr'
             # export lv_alt_col=' '
              export lv_alt_col1='key_col'
            #  export lv_key_col_col1='key_col,'
             # export lv_dkey_d_id='d_id,'
             echo "create multiset volatile table vt__usecase_tbl("$KEY_COL" varchar(100), user_act_nm varchar(100), usecase varchar(100)) primary index("$KEY_COL") on commit preserve rows;">>vt__usecase_tbl.out
             else
              export lv_key_col='1$KEY_COL'
			  export lv_col_nm='key_col'
              export lv_alt_col='0'
              export lv_alt_col1='key_col_chr'
           #   export lv_key_col_col1=' '
           #   export lv_dkey_d_id=' '
             echo "create multiset volatile table vt__usecase_tbl("$KEY_COL" bigint, user_act_nm varchar(100), usecase varchar(100)) primary index("$KEY_COL") on commit preserve rows;">>vt__usecase_tbl.out
             fi

                   export FILE_FIXDEL11="$PWD/file11.log"
                   export FILE_FIXDEL_OUT12="$PWD/tmp_msm12.out" 
bteq >> ${FILE_FIXDEL11} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/vt__usecase_tbl.out;
.export reset;
.export DATA file = '${FILE_FIXDEL_OUT12}';
.SET RECORDMODE OFF;
SEL 1 FROM tmp_msm WHERE session_id=$session AND user_act_nm='$user_act_nm' AND cma_rule_cond NOT LIKE '%all%other%' AND cma_rule_cond NOT LIKE '%rule%' AND cma_rule_cond IS NOT NULL AND TRIM(cma_rule_cond)<>' ';
.export reset;
.quit;
EOF

             if [ $(wc -l < tmp_msm12.out) -ge 1 ]; then

              if [ $li_debug_flg = "1" ]; then
                  echo "****`date` - Step 5 : Binary tree logic.."
               fi
			    touch tmp_msm1.out

while read line
do
session_id1=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
var_nm=`echo "$line"|cut -f6 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`

if [ $session_id1 = $session -a "$end_flg" = "Z" -a "$cma_rule_cond" != "?" -a "$var_nm" != "xxxxxxxx" ]; then
echo "$line">>tmp_msm1.out
fi

done <tmp_msm.out
            echo "CREATE TABLE vt_var_0 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm AS var_nm," >>_$user_act_nm.out 
			echo "CAST( ' OR ' AS VARCHAR(4)) || var_nm ||CAST(  ' is null ) ' AS VARCHAR(15) ) AS newcol,
                                CAST( CASE WHEN cma_rule_cond NOT LIKE '% missing%' AND INDEX(cma_rule_cond,' to ') > 0 THEN OREPLACE(cma_rule_cond,'to','and_logic')
                                           WHEN cma_rule_cond LIKE '% missing%' AND INDEX(cma_rule_cond,' to ') > 0 THEN OREPLACE(SUBSTR(cma_rule_cond,1,INDEX(cma_rule_cond,' or ')),'to','and1_logic')
                                           WHEN INDEX(cma_rule_cond,'>') > 0    THEN cma_rule_cond
                                           WHEN INDEX(cma_rule_cond,'<') > 0    THEN cma_rule_cond
                                           WHEN INDEX(cma_rule_cond,'<>') > 0   THEN cma_rule_cond
                                           WHEN INDEX(cma_rule_cond,'!=') > 0   THEN cma_rule_cond
                                           WHEN INDEX(cma_rule_cond,',') > 0    THEN ' in (' || cma_rule_cond || ')'
                                           WHEN INDEX(cma_rule_cond,'missing') > 0   THEN OREPLACE(cma_rule_cond,'missing', ' is null ')
                                           ELSE ' = ' ||(CASE WHEN  IDN_IsNumeric(cma_rule_cond)=0 THEN ''''||cma_rule_cond||'''' ELSE cma_rule_cond END)
                                END AS VARCHAR(8000)) cond">>_$user_act_nm.out
            echo "FROM tmp_msm WHERE session_id="$session" AND end_flg = 'Z' AND cma_rule_cond IS NOT NULL AND var_nm <> 'xxxxxxxx') with data;">>_$user_act_nm.out
                #echo "CREATE TABLE vt_var_1 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, cond|| ' ' ||newcol as cond from vt_var_0 WHERE cond LIKE '%and1_logic%') with data;">>_$user_act_nm.out
				#echo "CREATE TABLE vt_var_2 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and1_logic','and_logic') as cond from vt_var_1) with data;">>_$user_act_nm.out
				#echo "CREATE TABLE vt_var_3 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol,'between ' ||cond as cond from vt_var_2 WHERE cond LIKE '%and_logic%') with data;">>_$user_act_nm.out
				#echo "CREATE TABLE vt_var_4 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and_logic','and') as cond from vt_var_3) with data;">>_$user_act_nm.out
            echo "CREATE TABLE vt_var_1 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, case when cond LIKE '%and1_logic%' then cond|| ' ' ||newcol else cond end as cond from vt_var_0) with data;">>_$user_act_nm.out
            echo "CREATE TABLE vt_var_2 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and1_logic','and_logic') as cond from vt_var_1) with data;">>_$user_act_nm.out
            echo "CREATE TABLE vt_var_3 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol,case when cond LIKE '%and_logic%' then 'between ' ||cond else cond end as cond from vt_var_2 WHERE cond LIKE '%and_logic%') with data;">>_$user_act_nm.out
            echo "CREATE TABLE vt_var_4 as (SELECT usecase_child, usecase_parent, musecase_nm, var_nm,newcol, OREPLACE(cond,'and_logic','and') as cond from vt_var_3) with data;">>_$user_act_nm.out
            echo " sel trim(usecase_child),trim(usecase_parent),trim(musecase_nm),trim(var_nm),trim(newcol),trim(cond) from vt_var_4;">>_$user_act_nm.out

                echo " sel trim(usecase_child),trim(usecase_parent),trim(musecase_nm),trim(var_nm),trim(newcol),trim(cond) from vt_var_4;">>_$user_act_nm.out

 export FILE_FIXDEL12="$PWD/file12.log"
 #export FILE_FIXDEL_OUT13="$PWD/vt_var.out" 
bteq >> ${FILE_FIXDEL12} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/_$user_act_nm.out;
.quit;
EOF

			  # Log
               if [ $li_debug_flg = "1" ]; then
                  echo "****`date` - Step 6 : All other condition check for given usecaseation tree.."
               fi
             #awk -F '|' ' { print $6 }' vt_var.out>cond.out
           #  export cond1=`cat vt_var.out|grep other|wc -l|sed -e 's/^ *//'|sed -e 's/ *$//'`
			 cond1=0
			  #all other

			 if [ $cond1 -ge 1 ]; then

              cat vt_var.out|grep other|sed -e 's/^ *//'|sed -e 's/ *$//'>cond2.out
               awk -F '|' ' { print $4 }' cond2.out>cond3.out
             awk '{print }' cond3.out | sort | uniq >vt_var1.out

             #loop3
			 while read line
            do
            var_nm=`echo $line|cut -f1 -d"|"`
			 
             awk -F '|' ' { print $1, $2, $3, $6 }' cond2.out>cond4.out
             sort -k3 -n cond4.out>vt_var2.out
             awk -F '|' ' { print $2 }' vt_var2.out>cond5.out
			 awk '{print }' cond5.out | sort | uniq >vt_var3.out
			 
			    #loop4
				 while read line
                 do
                 var_nm=`echo $line|cut -f1 -d"|"`
				
			         comm -23 vt_var2.out vt_var3.out >vt_var4.out
					 export li_cma_idx=`cat vt_var4.out | cut -f1 -d " " | sort -nr | tail -1`
					 cat vt_var4.out|grep other|sed -e 's/^ *//'|sed -e 's/ *$//'>cond6.out
					 export li_max_cma_idx=`cat cond6.out | cut -f1 -d " " | sort -nr | head -1`
					 export lv_cma_rule_cond_stmt="null"
					 
					 #loop5
					  if [ $li_cma_idx > $li_max_cma_idx ]; then
					 
					       comm -2 vt_var4.out cond6.out >cond7.out
						   while read line
                           do
                           usecase_child=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           #var_nm=`echo "$line"|cut -f6 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           #cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           #end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           
                           if [ $usecase_child = $li_cma_idx -a $usecase_child < $li_max_cma_idx -a ]; then
                           export lv_cma_rule_cond=`awk -F '|' ' { print $4 }' cond7.out`
                           fi
			               done <cond7.out
			               cat $lv_cma_rule_cond|grep =|sed -e 's/^ *//'|sed -e 's/ *$//'>cond8.out
						   cat $lv_cma_rule_cond|grep in|sed -e 's/^ *//'|sed -e 's/ *$//'>cond9.out
			               if [ $(wc -l < cond8.out) -ge 1 ]; then
			                    export lv_cma_rule_cond=`echo $lv_cma_rule_cond | cut -c1,3`
			               elif [ $(wc -l < cond9.out) -ge 1 ]; then
						        export cond10=`echo $lv_cma_rule_cond | wc -l`
                                cond10=`expr $cond10 - 6`
						        export lv_cma_rule_cond=`awk ' { print substr($lv_cma_rule_cond,6,$cond10) }'`
						   fi
						   if [$lv_cma_rule_cond_stmt = "null"]; then
						         export lv_cma_rule_cond_stmt='not in('$lv_cma_rule_cond
						   elif [ $li_cma_idx = $li_max_cma_idx ]; then
						         export lv_cma_rule_cond_stmt=$lv_cma_rule_cond_stmt')'
						   else
						          export lv_cma_rule_cond_stmt=$lv_cma_rule_cond_stmt', '$lv_cma_rule_cond
						  fi
						  export li_cma_idx=`expr $li_cma_idx + 1`
			             
						  fi #loop5 end
			             #UPDATE a
                         #                       FROM  vt_var a, vt_var4 b
                         #                       SET   cond = lv_cma_rule_cond_stmt
                         #                       WHERE a.var_nm=lv_var_nm1
                         #                       AND   a.var_nm=b.var_nm
                         #                       AND   a.usecase_parent=b.usecase_parent
                         #                       AND   a.usecase_child=b.usecase_child
                         #                       AND   a.usecase_child=li_max_cma_idx 
                         #                       AND   cond LIKE '%all%other%';
                         #                       DROP TABLE vt_var4;
                         #                       SET li_usecase_idx = li_usecase_idx + 1;
                         #                       IF li_usecase_idx > li_max_usecase_idx THEN
                         #                          LEAVE lb4;
						 done <vt_var3.out
				 done < vt_var1.out
			fi #all other
			
			while read line
                           do
                           session_id=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           #var_nm=`echo "$line"|cut -f6 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           #cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                           
                           if [ $session_id = $session -a "$end_flg" = "D" ]; then
                           awk -F '|' ' { print $4 }' tmp_msm.out>>cond11.out
                           fi
                           done <tmp_msm.out
                           export lv_def_mod=`awk '{print }' cond11.out | sort | uniq `
                           echo "lv_def_mod"$lv_def_mod
 export FILE_FIXDEL17="$PWD/file17.log"
#export FILE_FIXDEL_OUT18="$PWD/vt_varr.out" 
bteq >> ${FILE_FIXDEL17} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_var_5  as (select  xx.usecase_child, yy.usecase_parent,xx.musecase_nm,xx.var_nm,xx.newcol,xx.cond FROM vt_var_4 xx,tmp_msm yy WHERE session_id=$session  AND xx.usecase_parent = yy.usecase_child AND yy.end_flg = 'N' AND NULLIF(TRIM(yy.var_nm),'') IS NOT NULL) with data;
.quit;
EOF

						
						# Log
                        if [ $li_debug_flg = "1" ]; then
                           echo "****`date` - Step 7    : creating vt_final table.."
                        fi
						echo "CREATE TABLE vt_mname1 as (SELECT usecase_child, usecase_parent, musecase_nm FROM tmp_msm WHERE session_id = "$session" AND end_flg='Y') with data;
                        CREATE TABLE vt_final_0 as (SELECT a.*, b.usecase_parent AS newp_id, CASE WHEN cond LIKE '%is null ) ' THEN ' when ('||b.var_nm||' '||cond ELSE ' when '||b.var_nm||' '||cond  END AS exp1 FROM vt_mname1 a, vt_var_5 b WHERE a.usecase_parent = b.usecase_child) with data;">vt_final.out
						
                   export FILE_FIXDEL13="$PWD/file13.log"
				   
bteq >> ${FILE_FIXDEL13} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.SET RECORDMODE OFF;
.RUN FILE = $HOME/vt_final.out;
.quit;
EOF

                   # Log
                         if [ $li_debug_flg = "1" ]; then
                            echo "****`date` - Step 8    : Entering first loop in preparing the sql query.."
                         fi
  i=0
  k=1
while [ $i -eq 0 ]
do
                  export FILE_FIXDEL21="$PWD/file21.log"
                  export FILE_FIXDEL_OUT23="$PWD/activity_count.out"

m=`expr $k + 1`
l=`expr $k - 1`
bteq >> ${FILE_FIXDEL21} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_final_$k as (select xx.usecase_child,xx.usecase_parent,xx.musecase_nm,xx.newp_id,CASE WHEN yy.cond LIKE '%is null ) ' THEN xx.exp1 || ' and (' || yy.var_nm ELSE xx.exp1 || ' and ' || yy.var_nm END as exp1 FROM vt_final_$l xx,vt_var_5 yy  WHERE xx.newp_id = yy.usecase_child) with data;
create table vt_final_$m as (select xx.usecase_child,xx.usecase_parent,xx.musecase_nm,yy.usecase_parent as newp_id,CASE WHEN yy.cond LIKE '%is null ) ' THEN xx.exp1 || ' and (' || yy.var_nm ELSE xx.exp1 || ' and ' || yy.var_nm END as exp1 FROM vt_final_$k xx,vt_var_5 yy  WHERE xx.newp_id = yy.usecase_child) with data;
drop table vt_final_$k;
drop table vt_final_$l;
.export DATA file = '${FILE_FIXDEL_OUT23}';
.SET RECORDMODE OFF;
sel count(1) from vt_final_$m;
.export reset;
.quit;
EOF

		export activity_count=`tail -1 $FILE_FIXDEL_OUT23|sed -e 's/^ *//'|sed -e 's/ *$//'`
						 echo "activity_count "$activity_count
						 if [ $activity_count -eq 0 ]; then
						 i=1
						 else
						 rm $FILE_FIXDEL_OUT23
						 fi
k=`expr $k + 2`
			done 
			
#vt_final_latest= vt_final_$m

echo "latest vt_final" vt_final_$m

                        # Log
                         if [ $li_debug_flg = "1" ]; then
                            echo "****`date` - Step 9    : Done with first loop.."
                         fi
                         echo "CREATE TABLE vt_statement1_0 as (SELECT usecase_child, CAST('insert into vt__usecase_tbl select unique_key_col,use_act as user_act_nm,cast((case '||exp1||' then '''||musecase_nm||'''' AS VARCHAR(22000)) AS stmt FROM vt_final_4
                         WHERE usecase_child = (SELECT MIN(usecase_child) FROM vt_final_4)) with data;">>vt_statement1.out
 
                   export FILE_FIXDEL14="$PWD/file14.log"

bteq >> ${FILE_FIXDEL14} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/vt_statement1.out;
.quit;
EOF
                        # Log
                         if [ $li_debug_flg = "1" ]; then
                            echo "****`date` - Step 10    : Entering second loop in preparing sql query.."
                         fi


j=0
n=1

while [ $j -eq 0 ]
do
o=`expr $n - 1`
#UPDATE xx
 #FROM vt_statement1 xx, vt_final yy SET stmt = xx.stmt || ' ' ||yy.exp1 || ' then '''||musecase_nm||'''', usecase_child = yy.usecase_child WHERE yy.usecase_child = (SELECT MIN(usecase_child) FROM vt_final WHERE usecase_child > xx.usecase_child);
 
 export FILE_FIXDEL23="$PWD/file23.log"
 export FILE_FIXDEL_OUT24="$PWD/activity_count1.out"

bteq >> ${FILE_FIXDEL23} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_statement1_$n as (select yy.usecase_child as usecase_child, xx.stmt || ' ' ||yy.exp1 || ' then '''||musecase_nm||'''' as stmt FROM vt_statement1_$o xx, vt_final_$m yy WHERE yy.usecase_child = (SELECT MIN(usecase_child) FROM vt_final_$m WHERE usecase_child > xx.usecase_child)) with data;
drop table vt_statement1_$o;
.export DATA file = '${FILE_FIXDEL_OUT24}';
.SET RECORDMODE OFF;
sel count(1) from vt_statement1_$n;
.export reset;
.quit;
EOF

export activity_count1=`tail -1 $FILE_FIXDEL_OUT24|sed -e 's/^ *//'|sed -e 's/ *$//'`
echo "activity_count1 "$activity_count1

if [ $activity_count1 -eq 0 ]; then

q=`expr $n + 1`
                 # export FILE_FIXDEL35="$PWD/file35.log" 
				 touch file35.log
                  #export FILE_FIXDEL_OUT08="$PWD/vt_statement1111.out" 


bteq >> file35.log <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
create table vt_statement1_$q as (select usecase_child,stmt ||' else '||'''$lv_def_mod'''||' end) as varchar(100)) as usecase from vt_ip_usecase_tbl)' as stmt from vt_statement1_$n) with data;
.quit;
EOF


j=1
else
rm  $FILE_FIXDEL_OUT24
fi
n=`expr $n + 1`
done 

 export FILE_FIXDEL36="$PWD/file36.log"
 export FILE_FIXDEL_OUT37="$PWD/sql_stmt_prep.out"

bteq >> ${FILE_FIXDEL23} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.export DATA file = '${FILE_FIXDEL_OUT37}';
.SET RECORDMODE OFF;
sel * from vt_statement1_$q;
.export reset;
.quit;
EOF


                         # Log
                         if [ $li_debug_flg = "1" ]; then
                            echo "****`date` - Step 11    : Done with second loop.."
                         fi
                         awk -F '|' ' { print $2 }' sql_stmt_prep.out>>cond12.out
                         export lv_sql_stmt=`awk '{print }' cond12.out | sort | uniq `
                          
                         # Log
                         if [ $li_debug_flg = "1" ]; then
                            echo "****`date` - Step 12    : Execute the prepared sql query to get the usecase details.."
                         fi 
                   export FILE_FIXDEL15="$PWD/file15.log"
                   #export FILE_FIXDEL_OUT07="$PWD/session7.out"
bteq >> ${FILE_FIXDEL13} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
CALL dbc.SysExecSQL($lv_sql_stmt);
CALL dbc.SysExecSQL('collect stats on vt__usecase_tbl index("$KEY_COL")');
DEL FROM tmp_msm WHERE session_id = $session;
DEL FROM tmp_msm WHERE user_act_nm = '$user_act_nm';
.export reset;
.quit;
EOF

                      # Log
                         if [ $li_debug_flg = "1" ]; then
                            echo "****`date` - Step 13    : write data to _musecase_decision table.."
                         fi 
                         
						 li_act_cd=$(<act_cd.out)
						 
					if [ $li_act_cd -eq 2 ]; then
						 # if [ $CHAR_CHECK -eq 1 ]; then
                        #  export lv_alt_col="'"$lv_d_id"'||trim(row_number() over (order by "$KEY_COL"))"
                        #  fi
						 # if [ $(wc -l <dec_ds1.out) -ge 1 ]; then
                         #
						 # echo "insert into _usecase_dec_aud_ds(feed_key,key_col, run_dt, user_act_nm,usecase) select feed_key, key_col, run_dt, user_act_nm,usecase from _usecase_dec_ds where feed_key = "$F_KEY" and user_act_nm ='"$user_act_nm"');
                         #  delete from _usecase_dec_ds where feed_key ="$F_KEY" and user_act_nm ='"$user_act_nm"';">>usecase_dec_aud_ds.out
                         #
						 #  fi
						   echo "insert into _usecase_dec_ds (feed_key, "$lv_col_nm ", run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,"li_var" from p_input_tbl_nm ;">>usecase_dec_aud_ds.out
						  
				    else
						 echo "insert into _usecase_dec_aud_ds (feed_key, "$lv_col_nm ",run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,"li_var" from p_input_tbl_nm ;">>usecase_dec_aud_ds.out
						 fi
						 
export FILE_FIXDEL15="$PWD/file15.log"
                   
bteq >> ${FILE_FIXDEL15} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/usecase_dec_aud_ds.out;
.export reset;
.quit;
EOF
			else
				       while read line
                       do
                       session_id=`echo "$line"|cut -f1 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
				       user_act_nm=`echo "$line"|cut -f3 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                       musecase_nm=`echo "$line"|cut -f4 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                       #cma_rule_cond=`echo "$line"|cut -f7 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                       #end_flg=`echo "$line"|cut -f8 -d"|"|sed -e 's/^ *//'|sed -e 's/ *$//'`
                       
                       if [ $session_id = $session -a $user_act_nm = $user_act_nm -a "$musecase_nm" != "?" -a "$musecase_nm" != '']; then
                       awk -F '|' ' { print $4 }' tmp_msm.out>>li_mdl_cnt_alloth1.out
				      
                       fi
			           done <tmp_msm.out
				       awk '{print }' li_mdl_cnt_alloth1.out | sort | uniq >li_mdl_cnt_alloth.out
				       export $li_mdl_cnt_alloth = wc -l < li_mdl_cnt_alloth.txt
                       if [ $li_mdl_cnt_alloth -eq 1 ]; then
				       lv_musecase_alloth=$(<li_mdl_cnt_alloth1.out)
				       echo "insert into vt__usecase_tbl select "$KEY_COL",'"$user_act_nm"','"$lv_musecase_alloth" from vt_ip_usecase_tbl";
				       else
				       li_error_cd=11
				       # Log
                               if [ $li_debug_flg = "1" ]; then
                                  echo "****`date` - Step 14    : binary tree logic is wrong for usecaseation "$user_act_nm".."
                               fi 
			          fi
				if [ "x"$li_error_cd = "x" ]; then
				 if [ $li_act_cd -eq 2 ]; then
				# if [ $CHAR_CHECK -eq 1 ]; then
                #  export lv_alt_col="'"$lv_d_id"'||trim(row_number() over (order by "$KEY_COL"))"
                # fi
						  if [ $(wc -l <dec_ds1.out) -ge 1 ]; then

						  echo "insert into _usecase_dec_aud_ds(feed_key,key_col,key_col_chr, dec_dt, run_dt, user_act_nm,usecase) select feed_key, key_col,key_col_chr, dec_dt, run_dt, user_act_nm,usecase from _usecase_dec_ds where feed_key = "$F_KEY" and user_act_nm ='"$user_act_nm"');
                           delete from _usecase_dec_ds where feed_key ="$F_KEY" and user_act_nm ='"$user_act_nm"';">>usecase_dec_ds.out
						   
						   fi
						   echo "insert into _usecase_dec_ds (feed_key, "$lv_col_nm ",dec_dt, run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,usecase from p_input_tbl_nm ;">>usecase_dec_ds.out
						  
						 else
						 echo "insert into _usecase_dec_aud_ds (feed_key, "$lv_col_nm ",dec_dt, run_dt, user_act_nm, usecase) select "$F_KEY" as feed_key,"$KEY_COL" as key_col,CURRENT_TIMESTAMP as run_dt,'"$user_act_nm"' as user_act_nm,usecase from p_input_tbl_nm ;">>usecase_dec_ds.out
						 fi
						 
export FILE_FIXDEL26="$PWD/file26.log"
                   
bteq >> ${FILE_FIXDEL26} <<-EOF
.logmech ${tera1_MECHANISMNAME}
.logon ${tera1_DBCNAME}/${tera1_USERNAME},${tera1_PASSWORD}
.set width 10000;
.set separator '${DELIMITER}';
.SET RECORDMODE OFF;
database ${TD1_DATABASE};
.RUN FILE = $HOME/usecase_dec_ds.out;
.export reset;
.quit;
EOF
			   fi
			   
				 
           fi #tmp_msm12


           fi #inner error code check
           fi #outer error code check
         # done <IP_usecase_NMs1.out
          fi #outer error code check
    else
    li_error_cd=13
    echo "usecaseATION EXISTANCE CHECK FAILED"
    fi
	fi #outer error check
echo " "
echo "Script completed Successfully . . :)"

---------- Post updated at 01:36 PM ---------- Previous update was at 01:32 PM ----------

Quote:
Originally Posted by Akshay Hegde
What have you tried so far?
-- provided the code
What output/errors do you get?
--not getting expected output, hope same logic can be simplified in unix, since we are not unix developer we did R&D to this stage
Please show attempts at a solution and specify what OS and versions are being used.
please share your thoughts, thanks
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
How to create SQL statement out of data using shell script?
gksenthilkumar
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: 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 ...... Shell Programming and Scripting
6
Shell Programming and Scripting
How to get full sql table data using shell script
ss135r
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;...... Shell Programming and Scripting
1
Shell Programming and Scripting
How to use sql data file in unix csv file as input to an sql query from shell
Nareshp
Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s...... Shell Programming and Scripting
2
Shell Programming and Scripting
how to use data in unix text file as input to an sql query from shell
rdhanek
Hi, I have data in my text file something like this. adams robert ahmed gibbs I want to use this data line by line as input to an sql query which i run by connecting to an oracle database from shell. If you have code for similar scenario , please ehlp. I want the output of the sql query...... Shell Programming and Scripting
7
Shell Programming and Scripting
how to assign sql output data to shell script variable
kattics
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... Shell Programming and Scripting
1
Shell Programming and Scripting