Find 5 lines and replace with 18 line in sql file where it contains multiple blocks.

Shell Programming and Scripting
1

My sql file xyz_abc.sql in this file there are multiple sql block in this block I need to find the following block

rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   *
FROM     &&tempName.*
WHERE    f is not null

and replace with following code

rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT  a a,b b,c c,d d,e e,f f,
 g g,h h,i i,j j,k k,l l,
 m m,n n,o o,p p,q q,r r,
 s s,t t,u u,v v,w w,x x,
 y y,z z,a a,b b,c c,d d,
 e e,f f,g g,h h,i i,j j,
 k k,l l,m m,n n,o o,p p,
 q q,r r,s s,t t,u u,v v,
 w w,x x,y y,z z,X1 X1, X2 X2,
 X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10, 
 X11 X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16,
 X17 X17, X18 X18, X19 X19, X20 X20, X21 X21, X22 X22, 
 X23 X23, X24 X24, X25 X25, X26 X26, X27 X27, X28 X28, 
 X29 X29, X30 X30
FROM     &&tempName.*
WHERE    f is not null
I have written the follwoing program using perl but its not working.
 
perl -i -pe 's/
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   *
FROM     &&tempName.*
WHERE    f is not null/
rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT  a a,b b,c c,d d,e e,f f,
 g g,h h,i i,j j,k k,l l,
 m m,n n,o o,p p,q q,r r,
 s s,t t,u u,v v,w w,x x,
 y y,z z,a a,b b,c c,d d,
 e e,f f,g g,h h,i i,j j,
 k k,l l,m m,n n,o o,p p,
 q q,r r,s s,t t,u u,v v,
 w w,x x,y y,z z,X1 X1, X2 X2,
 X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10, 
 X11 X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16,
 X17 X17, X18 X18, X19 X19, X20 X20, X21 X21, X22 X22, 
 X23 X23, X24 X24, X25 X25, X26 X26, X27 X27, X28 X28, 
 X29 X29, X30 X30
FROM     &&tempName.*
WHERE    EGTESTCD is not null/g' xyz_abc.sql

Please suggest.

2

Try...

 
sed 's/\(SELECT\) .*/\1 a a,b b,c c,d d,e e,f f,\
 g g,h h,i i,j j,k k,l l,\
 m m,n n,o o,p p,q q,r r,\
 s s,t t,u u,v v,w w,x x,\
 y y,z z,a a,b b,c c,d d,\
 e e,f f,g g,h h,i i,j j,\
 k k,l l,m m,n n,o o,p p,\
 q q,r r,s s,t t,u u,v v,\
 w w,x x,y y,z z,X1 X1, X2 X2,\
 X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10,\
 X11 X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16,\
 X17 X17, X18 X18, X19 X19, X20 X20, X21 X21, X22 X22,\
 X23 X23, X24 X24, X25 X25, X26 X26, X27 X27, X28 X28,\
 X29 X29, X30 X30/g' infile
3

Hi I tried with the above code but its not working.

I will explain you once again

In my xyz_abc.sql file there are multiple blocks.
Just imagine there are 25 blocks in xyz_abc.sql file in that block I need to find the following block.

rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   *
FROM     &&tempName.*
WHERE    EGTESTCD is not null

Once this get done in xyz_abc.sql. I need to replace with the following block

rem Subset Rows (&&tempName.*)
CREATE   VIEW &&tempName.* AS
SELECT   STUDYID STUDYID, DOMAIN DOMAIN, USUBJID USUBJID, ggg(y) EGSEQ,
                  EGGRPID EGGRPID, EGREFID EGREFID, EGSPID EGSPID, EGTESTCD
                  EGTESTCD, EGTEST EGTEST, EGCAT EGCAT, EGSCAT EGSCAT, EGPOS
                  EGPOS, EGORRES EGORRES, EGORRESU EGORRESU, EGNRIND EGNRIND,
                  EGSTRESC EGSTRESC, EGSTRESN EGSTRESN, EGSTRESU EGSTRESU,
                  EGSTAT EGSTAT, EGREASND EGREASND, EGXFN EGXFN, EGNAM EGNAM,
                  EGLOINC EGLOINC, EGMETHOD EGMETHOD, EGBLFL EGBLFL, EGDRVFL
                  EGDRVFL, EGEVAL EGEVAL, VISIT VISIT, VISITNUM VISITNUM,
                  VISITDY VISITDY, EGDTC EGDTC, EGDY EGDY, EGTPTNUM EGTPTNUM,
                  EGTPT EGTPT, EGELTM EGELTM, EGTPTREF EGTPTREF, X1 X1, X2 X2,
                  X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10, X11
                  X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16, X17 X17, X18
                  X18, X19 X19, X20 X20, X21 X21, X22 X22, X23 X23, X24 X24, X25
                  X25, X26 X26, X27 X27, X28 X28, X29 X29, X30 X30
FROM     &&tempName.*
WHERE    EGTESTCD is not null

Please suggest some code.Keep waiting for your reply.

4

To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags

```text
 and 
```

by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums

5

Save the replacement text in a file ( say repl_file ) and try,

perl -i.bak -0pe 's/rem.*?f is not null\n/qx(cat repl_file)/es' sql_file

Original file is saved in file.bak, just in case.

P.S.
The text is more readable if you place code tags around the samples/codes.

6

The above code is excuting but not replacing the file which is present in repl_file.Please suggest

---------- Post updated at 11:14 PM ---------- Previous update was at 11:10 PM ----------

Thanks in advance.and Thanks for your code.

Please suggest

7

(1) Yes but how is this block different from the other 24 ? You will need this information in order to identify and replace this block correctly (out of the 25).

(2) Are all the blocks identical ? If so, which block do you want replaced ? The 1st, 2nd, 25th ? Or all ?

To give us a better idea, post a short excerpt of your target file that clearly demonstrates the difference(s) between the block of interest and the rest.

tyler_durden

8

(1) Yes but how is this block different from the other 24? You will need this information in order to identify and replace this block correctly (out of the 25).

Ans: The 1st line and last line of this block are unique from other block.
The 1st line is �rem Subset Rows (&&tempName.*) and
The last line is �WHERE testvalue is not null�. Through this way I can identify this block.

(2) Are all the blocks identical? If so, which block do you want replaced? The 1st, 2nd, 25th? Or all?

   Ans: Not all block are identical only few of them are identical. The block which I want to replace is not present in this file \(xyz_abc.sql\). I want to find this identical block from xyz_abc.sql and then replace the block which is present in another file called repl_file.sql.

The xyz_abc.sql file contains 25 blocks. In xyz_abc.sql file I need to identify the following 5 lines block.

rem Subset Rows (&&tempName.)
CREATE VIEW &&tempName. AS
SELECT *
FROM &&tempName.*
WHERE testvalue is not null.

And then replace with following block which is present in repl_file.sql. This repl_file.sql contains only one block.

rem Subset Rows (&&tempName.)
CREATE VIEW &&tempName. AS
SELECT STUDYID STUDYID, DOMAIN DOMAIN, USUBJID USUBJID, ggg(y) ABSEQ,
ABGRPID ABGRPID, ABREFID ABREFID, ABSPID ABSPID, testvalue
testvalue, ABTEST ABTEST, ABCAT ABCAT, ABSCAT ABSCAT, ABPOS
ABPOS, ABOARES ABOARES, ABORRESU ABORRESU, ABNRIND ABNRIND,
ABSERESC ABSERESC, ABSTEESN ABSTEESN, ABSTRESU ABSTRESU,
ABSTAT ABSTAT, ABREBSND ABREBSND, ABXFN ABXFN, ABNAM ABNAM,
ABLOINC ABLOINC, ABMETHOD ABMETHOD, ABBLFL ABBLFL, ABDRVFL
ABDRVFL, ABEVAL ABEVAL, VISIT VISIT, VISITNUM VISITNUM,
VISITDY VISITDY, ABDTC ABDTC, ABDY ABDY, ABTPTNUM ABTPTNUM,
ABTPT ABTPT, ABELTM ABELTM, ABTPTFEF ABTPTFEF, X1 X1, X2 X2,
X3 X3, X4 X4, X5 X5, X6 X6, X7 X7, X8 X8, X9 X9, X10 X10, X11
X11, X12 X12, X13 X13, X14 X14, X15 X15, X16 X16, X17 X17, X18
X18, X19 X19, X20 X20, X21 X21, X22 X22, X23 X23, X24 X24, X25
X25, X26 X26, X27 X27, X28 X28, X29 X29, X30 X30
FROM &&tempName.*
WHERE testvalue is not null

MIC

---------- Post updated at 11:30 PM ---------- Previous update was at 03:52 PM ----------

(1) Yes but how is this block different from the other 24 ? You will need this information in order to identify and replace this block correctly (out of the 25).

The 1st line and last of this block is unique. thru this way can identify.

(2) Are all the blocks identical ? If so, which block do you want replaced ? The 1st, 2nd, 25th ? Or all ?

Not all the blocks are identical only few of them is identical. The block which i want replace is not present in xyz_abc.sql.

can we do this way.

my target file should be removed the identical block from xyz_abc.sql file and replace with repl_file.sql file block.

Please suggest.

Thanks in advance

9

Thanks, please address the last request as well:

The target file is "xyz_abc.sql".

tyler_durden

10

Please suggest some code for the above queryASAP.

Thanks in advance.