Hi ,
I am writing a script and more I dig into it more I see problems . I have this ddl file in which I want to replace the view names .But the problem is that some of the view names are preceeded with schema name and some not. I have this part in the script which takes care of view name which has the schema as prefix.

if grep VIEW ${target_schema}.$TN.${ecmdate}.sql
then
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' ${target_schema}.$TN.${ecmdate}.sql > $tmpfile_cln
cat $tmpfile_cln > ${target_schema}.$TN.${ecmdate}.sql
else
echo "file does not have a view in it "
fi

but the issue is what to do when there is no schema name in front of the view name like below :

#grep -i view DAYA2.dim_date.20080519.sql
CREATE VIEW DATE_ADJD (ADJD_DT_SYS_ID, ADJD_DAY_ABBR_CD, ADJD_DAY_NBR, ADJD_DAY_WK,
CREATE VIEW DATE_ADMIT (ADMIT_DT_SYS_ID, ADMIT_DAY_ABBR_CD, ADMIT_DAY_NBR,
CREATE VIEW DATE_ANCH_PROC (ANCH_PROC_DT_SYS_ID, ANCH_PROC_DAY_ABBR_CD,
CREATE VIEW OPSDM001.DATE_ANCH_STRT_EPSD (ANCH_STRT_EPSD_DT_SYS_ID, ANCH_STRT_EPSD_DAY_ABBR_CD,

I need output like this

#grep -i view DAYA2.dim_date.20080519.sql
CREATE VIEW DAYA2.VW_DATE_ADJD (ADJD_DT_SYS_ID, ADJD_DAY_ABBR_CD, ADJD_DAY_NBR, ADJD_DAY_WK,
CREATE VIEW DAYA2.VW_DATE_ADMIT (ADMIT_DT_SYS_ID, ADMIT_DAY_ABBR_CD, ADMIT_DAY_NBR,
CREATE DAYA2.VW_VIEW DATE_ANCH_PROC (ANCH_PROC_DT_SYS_ID, ANCH_PROC_DAY_ABBR_CD,
CREATE VIEW DAYA2.VW_DATE_ANCH_STRT_EPSD (ANCH_STRT_EPSD_DT_SYS_ID, ANCH_STRT_EPSD_DAY_ABBR_CD, (last line is fine )

I appreciate your suggestion !

If I understand your posting why can't you prepend the schema name to all views that don't have one while leaving out the ones that already have 'em.

You mean the following logic should work .

if grep VIEW file name
if grep ${source_schema}
then
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' ${target_schema}.$TN.${ecmdate}.sql > $tmpfile_cln
cat $tmpfile_cln > ${target_schema}.$TN.${ecmdate}.sql
else
append target_schema to view name and add VW_ to view name .
fi
else
echo "file does not have a view in it "
fi

Or I can append the schemaname before parsing the file for VW_ prefix .