Hi ,
I am trying to make this change work in my script but its not working.The idea is to grep for "CREATE VIEW" and then change view name from orig to VW_orig. but the problem comes when there is no schema prefix to the view name . the code I am using is

#!/bin/ksh
#####################################################
# use cut and awk to refine the view name
######################################################

if grep "CREATE VIEW" dim_copy.20080516.sql
then
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' dim_copy.20080516.sql > dim_copy.20080516.sql1
cat dim_copy.20080516.sql1 > dim_copy.20080516.sql
elif grep "CREATE VIEW" dim_copy.20080516.sql | grep -v OPSDM002
then
cat dim_copy.20080516.sql | grep -i "CREATE VIEW" | grep -v OPSDM002 | awk '{print "OPSDM002."$3 }' > dim_copy.20080516.sql1
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' dim_copy.20080516.sql1 > dim_copy.20080516.sql
else
echo "file does not have view in it "
fi

I have sample file like this

#grep -i view dim_copy.20080516.sql
CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR


After i run that script my result comes like this

#grep -i view dim_copy.20080516.sql
CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.VW_TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR

But I want result like follow :

CREATE VIEW OPSDM002.VW_MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.VW_TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR

So the first view name is not changed ......

Any help ........

If I understand this correctly I think the problem is you want to check for and add something to an item on one line for which the value is only provided in the next line. Only two approaches I can think of right now: reverse processing lines (tac) or grepping with line numbers. If you need an example please post more lines.

Thanks ,
let me rephrase it . I want to look for view in a ddl file and then replace it with target schema.VW_<old view name > .

For example,

CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR

to

CREATE VIEW OPSDM002.VW_MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.VW_TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR

Problem comes when in some lines view name doesn't have source schema as its prefix .

Here is the code I am using to fix it in vain

#cat view_add.shl
#!/bin/ksh
#####################################################
# use cut and awk to refine the view name
######################################################

if grep "CREATE VIEW" dim_copy.20080516.sql
then
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' dim_copy.20080516.sql > dim_copy.20080516.sql1
cat dim_copy.20080516.sql1 > dim_copy.20080516.sql
elif grep "CREATE VIEW" dim_copy.20080516.sql | grep -v OPSDM002
then
cat dim_copy.20080516.sql | grep -i "CREATE VIEW" | grep -v OPSDM002 | awk '{print "OPSDM002."$3 }' > dim_copy.20080516.sql1
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' dim_copy.20080516.sql1 > dim_copy.20080516.sql
else
echo "file does not have view in it "
fi

here is sh -x one

#sh -x view_add.shl
+ [[ -n 1 ]]
+ return
+ grep CREATE VIEW dim_copy.20080516.sql
CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR
+ sed -e /^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/ dim_copy.20080516.sql
+ 1> dim_copy.20080516.sql1
+ cat dim_copy.20080516.sql1
+ 1> dim_copy.20080516.sql

Thanks a lot for your effort ,

Daya

Apologies for using BaSH and unoptimised statements, OTOH its just a kludge. Run as "scriptname OPSDM002 VW dim_copy.20080516.sql" should echo to stdout what it could do. It doesn't op on the file at all unless you fix the echo line (maybe anchoring sed as well). Dunno, HTH.

thanks unSpawn ,
I am coming to another way of solving it . Lets say we don't want to deal with adding VW_ to any view name for time being. But my concern is how to append SchemaName to any view name who is missing it . For example
# grep -i "create view " dim_copy.20080516.sql
CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR
CREATE VIEW RAT_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR

to

CREATE VIEW OPSDM002.MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR
CREATE VIEW OPSDM002.RAT_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR

I hope it makes it bit easier .

Any input please .....

Oh well , i just found the change working but don't know how to substitute in the file .

The code part that is working is

#!/bin/ksh
#####################################################
# use cut and awk to refine the view name
######################################################

cat dim_copy.20080516.sql | grep -i "create view" | grep -v OPSDM002 | while read f1 f2 f3 f4 f5 f6 f7 f8 f9
do
f3="OPSDM002.${f3}"
echo " $f3 "
done

How do I substitute the new values of $f3 in the file ??

Thanks