Shell Programming and Scripting

Greetings Experts,

I do have some basic knowledge of Unix. The task I am trying to do through shell script is to generate the view script for all of the tables which is in YYYYMMDD format (I assume I am on Ksh).

I have certain tables that ends in YYYYMMDD format (eg: tbl_20150630)
For each month, there is a table with respective YYYYMMDD (eg: tbl_20150131, tbl_20150228, ...., tbl_20150630). There are many different tables (tbl_2_20150131,.....,tbl_2_20150630)
For each table of the same class (tbl_20150131, tbl_20150228, ...., tbl_20150630), I need to create the view (not a plain select on table; using some case and other logics) (vw_tbl_20150131, vw_tbl_20150228,...,vw_tbl_20150630)

Sample view structure and expected output (should be until tbl_20150630): (For simplicity using minimal columns and 2 tables)

Code:

create or replace view vw_tbl_20150131 as select (col1+col2) /* PAYMENTS */, (case when col3 = 'T' then col4*(0.9) else col4 end) /* DISCOUNT */ from tbl_20150131;

create or replace view vw_tbl_20150228 as select (col1+col2) /* PAYMENTS */, (case when col3 = 'T' then col4*(0.9) else col4 end) /* DISCOUNT */ from tbl_20150228;

Approach I followed is to pass the view_name and the list of YYYYMMDD views to be created and have the view body separated into some files with below content.

selectpart1.txt:

Code:

as select (col1+col2) /* PAYMENTS */, (case when col3 = 'T' then col4*(0.9) else col4 end) /* DISCOUNT */ from tbl_

yyyymmdd.txt: (please note that not all tables starts with 20150131; hence created them in separate file of valid values)

Code:

20150131
20150228
-----
20150630

What I have tried is (courtesy of my friends) the below (may not be the exact one as I cannot copy paste) which works fine. ( I am in mydir directory)

Code:

# $1 = vw_tbl_
touch mydir/view_tbl_creation.sql
for i in `cat mydir/yyyymmdd.txt`
do

temp=`cat mydir/selectpart1.txt`
echo "create or replace view "$1$i $temp$i ";\n" >> mydir/view_tbl_creation.sql

done

When I execute the above shell script, could see wherever the comments are present in the selectpart1.txt ( /* ) , there are list of files displayed in the output of the above one, which I guess is due to shell interpretation of /* (all files in the directory). When I replace the contents of file selectpart1.txt where comments are there ( /* ) with -- it works.

selectpart1.txt:

Code:

 as select (col1+col2) -- PAYMENTS , 
 (case when col3 = 'T' then col4*(0.9) else col4 end) -- DISCOUNT 
 from tbl_

and I get the output as expected. However, I prefer to retain the /* comments in the selectpart1.txt; How can I achieve this. Thanks for your time.

Note: I edit the selectpart1.txt to have the view body relative to the tables involved. (selectpart1.txt content doesn't change for vw_tbl_20150131, vw_tbl_20150228, ....., vw_tbl_20150630). But when the table class changes, I edit the selectpart1.txt accordingly which will be same for (vw_tbl_2_20150131, vw_tbl_2_20150228, ...., vw_tbl_2_20150630)

Try:

Code:

awk '
        NR == FNR {
                A[$1]
                next
        }
        {
                for ( k in A )
                        print "create or replace view vw_tbl_" k " " $0 k ";"
        }
' mydir/yyyymmdd.txt mydir/selectpart1.txt > mydir/view_tbl_creation.sql

If selectpart1.txt is multi record file:-

Code:

awk '
        NR == FNR {
                A[$1]
                next
        }
        {
                S = S ? S RS $0 : $0
        }
        END {
                for ( k in A )
                        print "create or replace view vw_tbl_" k " " S k ";"
        }
'  mydir/yyyymmdd.txt mydir/selectpart1.txt > mydir/view_tbl_creation.sql