matching pattern and replacement

Hi
I am trying to look for a view name in create view statement and then replace this view name with VW_ in grants line in my ddl file .

cat dim_provider.sql | grep -i "create view" | while read f1 f2 f3 f4 f5 f6 f7 f8 f9
do
new_vw=` echo "$f3" | cut -d "." -f2 `
sed -e 's/${new_vw}/VW_${new_vw}/g' dim_provider.sql > dim_provider.sql1
done

The sql file is

#grep -i grant dim_provider.sql
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP CLOSEL01 ;

I want the change to be

GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP CLOSEL01 ;

I don't know what needs to be done now .
Thanks

Well , I modified it to better one ( I guess ) but I don't get the output file as desired one .

The updated code is

cat dim_provider.sql | grep -i "create view" | while read f1 f2 f3 f4 f5 f6 f7 f8 f9
do
new_vw=` echo "$f3" | cut -d "." -f2 `
echo "${new_vw}"
cat dim_provider.sql | grep -i GRANT | grep -i ${new_vw} | while read f1 f2 f3 f4 f5 f6 f7 f8 f9
do
sed -e s/${new_vw}/VW_${new_vw}/g dim_provider.sql > dim_provider.sql1
done
done

and sh -x of shl is

#sh -x add_VW_grants.shl
+ [[ -n 1 ]]
+ return
+ cat dim_provider.sql
+ grep -i create view
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ + echo OPSDM002.PROVIDER_MBR_PRI
+ cut -d . -f2
new_vw=PROVIDER_MBR_PRI
+ echo PROVIDER_MBR_PRI
PROVIDER_MBR_PRI
+ cat dim_provider.sql
+ grep -i GRANT
+ grep -i PROVIDER_MBR_PRI
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_MBR_PRI/VW_PROVIDER_MBR_PRI/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_MBR_PRI/VW_PROVIDER_MBR_PRI/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_MBR_PRI/VW_PROVIDER_MBR_PRI/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_MBR_PRI/VW_PROVIDER_MBR_PRI/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ + echo OPSDM002.PROVIDER_REF
+ cut -d . -f2
new_vw=PROVIDER_REF
+ echo PROVIDER_REF
PROVIDER_REF
+ cat dim_provider.sql
+ grep -i GRANT
+ grep -i PROVIDER_REF
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_REF/VW_PROVIDER_REF/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_REF/VW_PROVIDER_REF/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_REF/VW_PROVIDER_REF/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_REF/VW_PROVIDER_REF/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ + echo OPSDM002.PROVIDER_SRVC
+ cut -d . -f2
new_vw=PROVIDER_SRVC
+ echo PROVIDER_SRVC
PROVIDER_SRVC
+ cat dim_provider.sql
+ grep -i GRANT
+ grep -i PROVIDER_SRVC
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_SRVC/VW_PROVIDER_SRVC/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_SRVC/VW_PROVIDER_SRVC/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_SRVC/VW_PROVIDER_SRVC/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_SRVC/VW_PROVIDER_SRVC/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
apsp8642:/dba_wk/dual_schema #

Any help is appreciated !

One way,

awk -F. -v OFS=. '/^[Gg][Rr][Aa][Nn][Tt]/{ $2="VW_"$2; print }'   filename

If you need only the lines with upper case GRANT then replace the search pattern with:

awk -F. -v OFS=. '/^GRANT/{ ... }' filename

Rubin ,
This will not solve what I need. Your code will change all the view names and table names whereever GRANT is there but I need to look for CREATE VIEW names and get the name of view and then change it to VW_ in the grant section .

Thanks

Some lines of the file dim_provider.sql and the desired output would be helpful.

Regards

Here is the input file first :

#egrep -i "view|grant " dim_provider.sql

CREATE VIEW OPSDM002.PROVIDER_MBR_PRI ( MBR_PRI_PROV_SYS_ID, MBR_PRI_COS_PROV_SPCL_CD,
CREATE VIEW OPSDM002.PROVIDER_REF ( REF_PROV_SYS_ID, REF_COS_PROV_SPCL_CD,
CREATE VIEW OPSDM002.PROVIDER_SRVC ( SRVC_PROV_SYS_ID, SRVC_COS_PROV_SPCL_CD,
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER LTREXL1 ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP UHCDMTST ;

Here is the output file now :

#egrep -i "view|grant " dim_provider.sql1

CREATE VIEW OPSDM002.PROVIDER_MBR_PRI ( MBR_PRI_PROV_SYS_ID, MBR_PRI_COS_PROV_SPCL_CD,
CREATE VIEW OPSDM002.PROVIDER_REF ( REF_PROV_SYS_ID, REF_COS_PROV_SPCL_CD,
CREATE VIEW OPSDM002.VW_PROVIDER_SRVC ( SRVC_PROV_SYS_ID, SRVC_COS_PROV_SPCL_CD,
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER LTREXL1 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP UHCDMTST ;

If you notice , the code made changes to the last name of the view . I don't know how it is working this way .
But sh -x show the correct output on screen .

Thanks

Sorry I forgot to post sh -x part of the code .

#sh -x add_VW_grants.shl
+ [[ -n 1 ]]
+ return
+ cat dim_provider.sql
+ grep -i create view
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ + echo OPSDM002.PROVIDER_MBR_PRI
+ cut -d . -f2
new_vw=PROVIDER_MBR_PRI
+ echo PROVIDER_MBR_PRI
PROVIDER_MBR_PRI
+ cat dim_provider.sql
+ grep -i GRANT
+ grep -i PROVIDER_MBR_PRI
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_MBR_PRI/VW_PROVIDER_MBR_PRI/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_MBR_PRI/VW_PROVIDER_MBR_PRI/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ + echo OPSDM002.PROVIDER_REF
+ cut -d . -f2
new_vw=PROVIDER_REF
+ echo PROVIDER_REF
PROVIDER_REF
+ cat dim_provider.sql
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ grep -i GRANT
+ grep -i PROVIDER_REF
+ sed -e s/PROVIDER_REF/VW_PROVIDER_REF/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_REF/VW_PROVIDER_REF/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ + echo OPSDM002.PROVIDER_SRVC
+ cut -d . -f2
new_vw=PROVIDER_SRVC
+ echo PROVIDER_SRVC
PROVIDER_SRVC
+ cat dim_provider.sql
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ grep -i GRANT
+ grep -i PROVIDER_SRVC
+ sed -e s/PROVIDER_SRVC/VW_PROVIDER_SRVC/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ sed -e s/PROVIDER_SRVC/VW_PROVIDER_SRVC/g dim_provider.sql
+ 1> dim_provider.sql1
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9
+ read f1 f2 f3 f4 f5 f6 f7 f8 f9

Thanks,

Are you try to make the chances in the file dim_provider.sql?
Again, post some lines of the file dim_provider.sql and the desired output.

Regards

This is input file

#cat dim_provider.sql
CONNECT TO TST103 ;
SET CURRENT SCHEMA OPSDM002 ;

CREATE TABLE OPSDM002.DIM_PROVIDER (
PROV_ID CHAR(7) NOT NULL ,
PROV_SYS_ID INTEGER NOT NULL ,
ZIP_CD CHAR(5) NOT NULL ,
UNIQ_PROV_SYS_ID BIGINT NOT NULL )
COMPRESS YES
IN TSOPS06 INDEX IN TSOPIX06 ;

CREATE ALIAS OPSDM002.PROVIDER FOR OPSDM002.DIM_PROVIDER;
CREATE VIEW OPSDM002.PROVIDER_MBR_PRI ( MBR_PRI_PROV_SYS_ID, MBR_PRI_COS_PROV_SPCL_CD,
MBR_PRI_DEA_ALPH_NBR, MBR_PRI_DEA_NBR, MBR_PRI_MPIN, MBR_PRI_NAT_ASSOC_BD_PHRM_NBR,
MBR_PRI_PROV_FST_NM, MBR_PRI_PROV_ID, MBR_PRI_PROV_LST_NM, MBR_PRI_SITE_CD,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;

CREATE VIEW OPSDM002.PROVIDER_REF ( REF_PROV_SYS_ID, REF_COS_PROV_SPCL_CD,
REF_DEA_ALPH_NBR, REF_DEA_NBR, REF_MPIN, REF_NAT_ASSOC_BD_PHRM_NBR,
REF_PROV_FST_NM, REF_PROV_ID, REF_PROV_LST_NM, REF_SITE_CD, REF_TIN,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;

CREATE VIEW OPSDM002.PROVIDER_SRVC ( SRVC_PROV_SYS_ID, SRVC_COS_PROV_SPCL_CD,
SRVC_DEA_ALPH_NBR, SRVC_DEA_NBR, SRVC_MPIN, SRVC_NAT_ASSOC_BD_PHRM_NBR,
SRVC_PROV_FST_NM, SRVC_PROV_ID, SRVC_PROV_LST_NM, SRVC_SITE_CD,
SRVC_TIN, SRVC_ZIP_CD, SRVC_UNIQ_PROV_SYS_ID, SRVC_LOAD_DT, SRVC_UPDT_DT)
FROM DIM_PROVIDER ;

GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP UHCDMTST ;

GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER LTREXL1 ;

GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP UHCDMTST ;

CREATE INDEX OPSDM002.RPL_IDX2_PROVIDER_TIN ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.RPL_IDX2_TIN_UNIQ_PROV ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC,
UNIQ_PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
COMMIT WORK ;

The desired output is :

GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER LTREXL1 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP UHCDMTST ;

Thanks

This saves the output in newfile:

awk '/^GRANT/{sub("OPSDM002.","OPSDM002.VW_")}1' dim_provider.sql > newfile

Regards

Or with sed:

sed '/^GRANT/s/OPSDM002./OPSDM002.VW_/' dim_provider.sql > newfile

Regards

Franklin ,
May be I should have mentioned it earlier . This particular file doesn't have any grants on table. What if there are grants for the table too in the file . Your code will change all tables but I need only view names to be changed . So my approach is to grep the view name from create view statement and then make changes to that view name in grant part of ddl file . I am going to modify the ddl file and then cat it .

here is the modified file :

#cat dim_provider.sql
CONNECT TO TST103 ;
SET CURRENT SCHEMA OPSDM002 ;

CREATE TABLE OPSDM002.DIM_PROVIDER (
PROV_ID CHAR(7) NOT NULL ,
PROV_SYS_ID INTEGER NOT NULL ,
ZIP_CD CHAR(5) NOT NULL ,
UNIQ_PROV_SYS_ID BIGINT NOT NULL )
COMPRESS YES
IN TSOPS06 INDEX IN TSOPIX06 ;

CREATE ALIAS OPSDM002.PROVIDER FOR OPSDM002.DIM_PROVIDER;
CREATE VIEW OPSDM002.PROVIDER_MBR_PRI ( MBR_PRI_PROV_SYS_ID, MBR_PRI_COS_PROV_SPCL_CD,
MBR_PRI_DEA_ALPH_NBR, MBR_PRI_DEA_NBR, MBR_PRI_MPIN, MBR_PRI_NAT_ASSOC_BD_PHRM_NBR,
MBR_PRI_PROV_FST_NM, MBR_PRI_PROV_ID, MBR_PRI_PROV_LST_NM, MBR_PRI_SITE_CD,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;

CREATE VIEW OPSDM002.PROVIDER_REF ( REF_PROV_SYS_ID, REF_COS_PROV_SPCL_CD,
REF_DEA_ALPH_NBR, REF_DEA_NBR, REF_MPIN, REF_NAT_ASSOC_BD_PHRM_NBR,
REF_PROV_FST_NM, REF_PROV_ID, REF_PROV_LST_NM, REF_SITE_CD, REF_TIN,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;

CREATE VIEW OPSDM002.PROVIDER_SRVC ( SRVC_PROV_SYS_ID, SRVC_COS_PROV_SPCL_CD,
SRVC_DEA_ALPH_NBR, SRVC_DEA_NBR, SRVC_MPIN, SRVC_NAT_ASSOC_BD_PHRM_NBR,
SRVC_PROV_FST_NM, SRVC_PROV_ID, SRVC_PROV_LST_NM, SRVC_SITE_CD,
SRVC_TIN, SRVC_ZIP_CD, SRVC_UNIQ_PROV_SYS_ID, SRVC_LOAD_DT, SRVC_UPDT_DT)
FROM DIM_PROVIDER ;

GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP DSDBDEV ;
GRANT INSERT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP DSDBDEV ;

GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_MBR_PRI TO GROUP UHCDMTST ;

GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.PROVIDER_REF TO USER LTREXL1 ;

GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.PROVIDER_SRVC TO GROUP UHCDMTST ;

CREATE INDEX OPSDM002.RPL_IDX2_PROVIDER_TIN ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.RPL_IDX2_TIN_UNIQ_PROV ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC,
UNIQ_PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
COMMIT WORK ;

I added the grants on table too which is most likely to be all ddls . How do we handle this .

I thank you for your time on this

You can give another regexp to change the specific lines:

awk '/^GRANT/&&/DIM_PROVIDER/{sub("OPSDM002.","OPSDM002.VW_")}1' dim_provider.sql > newfile

Regards

Sorry, I was some confused with your files, the awk command should be:

awk '/^GRANT/&&/DIM_PROVIDER/{sub("DIM_PROVIDER","VW_DIM_PROVIDER")}1' dim_provider.sql > newfile

Regards

Thanks Franklin ,
I think you meant this when you wrote first .

awk '/^GRANT/ && !/DIM_PROVIDER/{sub("DIM_PROVIDER","VW_DIM_PROVIDER")}1' dim_provider.sql > newfile

It works absolutely fine if I use only dim_provider file . But I am having issues with the larger code in which I pass the table name as $TN and the schemaname is ${target_schema} .
Can I substitute these parameters something like this ???

awk '/^GRANT/ && !/$TN/{sub(${target_schema}.,${target_schema}."VW_")}1' ${target_schema}.$TN.${ecmdate}.sql > $tmpfile_cln

but code is not working .When I run it as sh -x <script name> <dbname > <source schema > <target schema >

awk /^GRANT/ && !/$TN/{sub(${target_schema}.,${target_schema}."VW_")}1 OPSDM002.fact_physician.20080530.sql
+ 1> /tmp/tmpfile_cln
syntax error The source line is 1.
The error context is
/^GRANT/ && >>> !/$TN/{sub(${ <<<
awk: The statement cannot be correctly parsed.
The source line is 1.
awk: Quitting
The source line is 1.

Help please !
Thanks

Oops i meant this code is working fine only for dim_provider.sql file .

awk '/^GRANT/ && !/DIM_PROVIDER/{sub("OPSDM002.","OPSDM002.VW_")}1' dim_provider.sql > dim_provider.sql1

Rest of the question is same like above .

But I am having issues with the larger code in which I pass the table name as $TN and the schemaname is ${target_schema} .
Can I substitute these parameters something like this ???

awk '/^GRANT/ && !/$TN/{sub(${target_schema}.,${target_schema}."VW_")}1' ${target_schema}.$TN.${ecmdate}.sql > $tmpfile_cln

but code is not working .When I run it as sh -x <script name> <dbname > <source schema > <target schema >

awk /^GRANT/ && !/$TN/{sub(${target_schema}.,${target_schema}."VW_")}1 OPSDM002.fact_physician.20080530.sql
+ 1> /tmp/tmpfile_cln
syntax error The source line is 1.
The error context is
/^GRANT/ && >>> !/$TN/{sub(${ <<<
awk: The statement cannot be correctly parsed.
The source line is 1.
awk: Quitting
The source line is 1.

Help please !
Thanks

How do I make this code dynamic ?

I hope i am not confusing anymore .

Thanks ,

You can use shell variables as follow:

Regards

Mr Franklin ,
I am having issues with unix . Please help me here once again as i add more awk to my code . It is sh -x

+ awk -v tn=fact_physician -v ts=OPSDM002 /^GRANT/ && !/$tn/{sub($ts,$ts."VW_")}1 OPSDM002.fact_physician.20080530.sql
+ 1> /tmp/tmpfile_cln
syntax error The source line is 1.
The error context is
/^GRANT/ && >>> !/$tn/{sub($ts,$ts. <<< "VW_")}1
awk: The statement cannot be correctly parsed.
The source line is 1.

The code part is

awk -v tn="$TN" -v ts="$target_schema" '/^GRANT/ && !/$tn/{sub($ts,$ts."VW_")}1' ${target_schema}.$TN.${ecmdate}.sql > $tmpfile_cln

even without double quotes , the output file is of size 0 .

Thanks again ,

Dont use a "$" before variables within the awk command:

awk -v tn="$TN" -v ts="$target_schema" '/^GRANT/ && !/tn/{sub(ts,ts."VW_")}1' ${target_schema}.$TN.${ecmdate}.sql > $tmpfile_cln

Regards

Its not working either .

+ awk -v tn=fact_physician -v ts=OPSDM002 /^GRANT/ && !/tn/{sub(ts,ts."VW_")}1 OPSDM002.fact_physician.20080530.sql
+ 1> /tmp/tmpfile_cln
syntax error The source line is 1.
The error context is
/^GRANT/ && >>> !/tn/{sub(ts,ts. <<< "VW_")}1
awk: The statement cannot be correctly parsed.
The source line is 1.

code is

awk -v tn="$TN" -v ts="$target_schema" '/^GRANT/ && !/tn/{sub(ts,ts."VW_")}1' ${target_schema}.$TN.${ecmdate}.sql > $tmpfile_cln

Thanks,