help on script

mohan705 · September 8, 2008, 9:31am

I have around 200 tables need to check the following column sequence instead of checking manually every table ,if sequence is different ,
it should return the table name so that I can check that table and re-arrange the sequence.Any help it should be greatful.

NEW_Cd
Factor_Desc
Tab_name

Tab1.txt

CREATE SET TABLE xx1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
SECT_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
NEW_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Factor_Desc VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Tab_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
Code1 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL
)
PRIMARY INDEX ( Calculation_Factor_Cd )
;

CREATE SET TABLE xx2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
NEW_Cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Tab_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
Factor_Desc VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Code1 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL
)
PRIMARY INDEX ( Calculation_Factor_Cd )
;

Thanks,
MR

cfajohnson · September 8, 2008, 5:50pm

awk '
/^CREATE SET TABLE/ { tablename = $4 }
(/^Factor_Desc/ && last != "NEW_Cd") ||
 (/^Tab_name/ && last != "Factor_Desc" ) { printf "Error: %s\n", tablename }
{ last = $1 }
' "$FILE" | uniq

##

mohan705 · September 9, 2008, 4:44am

I am getting the below error when executing the below one

awk ' /^CREATE SET TABLE/ { tablename = $4 } (/^DBS_ECC_Code_Table_Name/ && last != "DBS_New_Cd") || (/^Tab_name/ && last !"DBS_ECC_Code_Table_Name" ) { printf "Error: %s\n", tablename } { last = $1 } ' a.txt | uniq

syntax error The source line is 1.
The error context is
/^CREATE SET TABLE/ { tablename = $4 } (/^DBS_ECC_Code_Table_Name/ && last != "DBS_New_Cd") || (/^Tab_name/ && last >>> ! <<< "DBS_ECC_Code_Table_Name" ) { printf "Error: %s\n", tablename } { last = $1 }
awk: Quitting
The source line is 1.

Thanks,
MR

mohan705 · September 9, 2008, 5:17am

Hi

Please ignore my previous post .When executing the below one ,Its not giving any result even if sequence is not in order

awk '/^CREATE SET TABLE/ { tablename = $4 }(/^Factor_Desc/ && last != "NEW_Cd") ||(/^Tab_name/ && last != "Factor_Desc" ) { printf "Error: %s\n", tablename }{ last = $1 }
' a.txt | uniq

Thanks,
MR

cfajohnson · September 9, 2008, 12:33pm

First, when posting code, please enclose it in CODE tags.

Second, do not put your code all on one line. It makes it hard to read.

Third, the above script works for me when used with the data you supplied.

mohan705 · September 9, 2008, 10:57pm

Hi cfajohnson

Sorry it is my mistake ,its working fine.Thanks for your great help .

I have one more query ,I want replace null value of end_dt field to default value "1999-01-01' in all my insert scripts(It has around 200 scripts).Any help it should be greatful.

insert into PRD_TYPE
(
Period_Cd,
Period_Desc,
Ind1,
IND2,
Start_Dt,
End_Dt
)
select
Period_Cd,
Period_Desc,
NULL,
NULL,
date '1900-01-01' as START_DT,
NULL
from PRD_TYPE_2;

I am expecting the below o/p

insert into PRD_TYPE
(
Period_Cd,
Period_Desc,
Ind1,
IND2,
Start_Dt,
End_Dt
)
select
Period_Cd,
Period_Desc,
NULL,
NULL,
date '1900-01-01' as start_dt,
date '1999-01-01' as end_dt
from PRD_TYPE_2;

Thanks
MR

learnbash · September 10, 2008, 2:07am

I think its line number 16

try this

linenumber=16
sed -i "${linenumber}s/\<NULL\>/date '1999-01-01' as end_dt /g" file-name

Thanks,
Bash

era · September 10, 2008, 2:16am

Or if the line number might vary, but it's the NULL alone on a line immediately after the START_DT line,

sed -i '/as START_DT/,/^NULL$/s/^NULL$/'"date '1999-01-01' as end_dt /" filename

If your sed doesn't support the -i option, you will need to store the results in a temporary file and move it back on top of the original file.

mohan705 · September 10, 2008, 3:02am

Hi era,
Its not working .(NULL alone on a line immediately after the START_DT line in all my scripts)

sed '/as START_DT/,/^NULL$/s/^NULL$/'"date '1999-01-01' as end_dt /" c.txt

insert into PRD_TYPE
(
Period_Cd,
Period_Desc,
Ind1,
IND2,
Start_Dt,
End_Dt
)
select
Period_Cd,
Period_Desc,
NULL,
NULL,
date '1900-01-01' as START_DT,
NULL
from PRD_TYPE_2;

Thanks,
MR

era · September 10, 2008, 3:04am

Then I guess the NULL is not really alone on the line; do you have spaces on either side? Fixing that is a trivial exercise. Or use the regex suggested earlier by learnbash, i.e. \<NULL\> instead of ^NULL$

mohan705 · September 10, 2008, 3:07am

Hi Era,

Sorry ,Its working (spaces infornt Null and removed ^ before null).Thanks for your help.How can do it same thing in perl

sed '/as START_DT/,/NULL$/s/NULL$/'"date '1999-01-01' as end_dt /" c.txt

Thanks,
MR

era · September 10, 2008, 3:20am

In Perl, the line range operator is different -- actually there are two different operators to choose from, ... or .. (I leave it to you to figure out from the documentation what their differences are).

perl -pe 'if (/as START_DT/ .. /NULL$/) { s/NULL$/'"date '1999-01-01' as end_dt / }" c.txt

Or in Perl, you can do matching across line boundaries with -0777:

perl -0777 -pe 's/(as START_DT,\s*\n\s*)NULL\n/$1'"date '1999-01-01' as end_dt\n/" c.txt

There is also a s2p script which ships with the Perl distribution which automatically translates sed scripts to Perl scripts (although the end result is not always particularly elegant, and sometimes even wrong, I hear).

mohan705 · September 10, 2008, 3:27am

Hi Era,

Thanks for your great help .

Thanks,
MR

mohan705 · September 11, 2008, 3:23am

Hi cfajohnson ,

I am checking the following sequence in my file,there are 3 tables last 3 columns having similar sequnce but
when calling the below script its not suppose to give any table name but its giving two table name.Please help

Standardized_Ind
New_Cd
Table_Name

FILE=$1
awk ' /^CREATE SET TABLE/ { tablename = $4 }
(/^Table_Name/ && last != "Standardized_Ind") ||
(/^New_Cd/ && last != "Table_Name" )
{ printf "Error: %s\n", tablename } { last = $1 } ' "$FILE" | uniq

calling unix script

#ab.sh b.txt

Error: XX
Error: XX2

b.txt

CREATE SET TABLE XX ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Categ_Cd VARCHAR(10) CHARACTER ,
Categ_Desc VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) ) ;

CREATE SET TABLE XX1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Categ_Cd1 VARCHAR(10) ,
Categ_Desc1 VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) ) ;

CREATE SET TABLE XX2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Financial_Asset_Categ_Cd VARCHAR(10) ,
Financial_Asset_Categ_Desc VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) );

Thanks,
MR

era · September 11, 2008, 6:42am

CREATE SET TABLE apparently isn't at beginning of line (that's the meaning of ^) or you have erratic spacing. Both of these can be corrected, but we can't tell from the data you posted. (It could help if you used code tags when posting your samples.)

cfajohnson · September 11, 2008, 1:30pm

You are checking for:
Standardized_Ind
Table_Name
New_Cd

instead of
Standardized_Ind
New_Cd
Table_Name

mohan705 · September 12, 2008, 5:53am

Hi cfajohnson
Still I am getting error (when check for following sequence)
Standardized_Ind
New_Cd
Table_Name


FILE=$1
awk -F " " ' /^CREATE SET TABLE/ { tablename = $4 }
 (/^New_Cd/ && last != "Standardized_Ind") || (/^Table_Name/ && last != "^New_Cd" )
 { printf "Error: %s\n", tablename } { last = $1 } ' "$FILE" | uniq

##

I am getting the below error message when calling the above script
ab.sh b.txt
Error: XX
Error: XX1
Error: XX2


b.txt
CREATE SET TABLE XX ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Categ_Cd VARCHAR(10) CHARACTER ,
Categ_Desc VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) ) ;
CREATE SET TABLE XX1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Categ_Cd1 VARCHAR(10) ,
Categ_Desc1 VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) ) ;
CREATE SET TABLE XX2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Financial_Asset_Categ_Cd VARCHAR(10) ,
Financial_Asset_Categ_Desc VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) );

Thanks,
MR

cfajohnson · September 12, 2008, 2:18pm

mohan705:

FILE=$1
awk -F " " ' /^CREATE SET TABLE/ { tablename = $4 }
 (/^New_Cd/ && last != "Standardized_Ind") || (/^Table_Name/ && last != "^New_Cd" )
 { printf "Error: %s\n", tablename } { last = $1 } ' "$FILE" | uniq

##

The action must start on the same line as the pattern:

FILE=$1
awk -F " " ' /^CREATE SET TABLE/ { tablename = $4 }
 (/^New_Cd/ && last != "Standardized_Ind") ||
 (/^Table_Name/ && last != "^New_Cd" ) { ## Start action here
 printf "Error: %s\n", tablename }
 { last = $1 } ' "$FILE" | uniq

mohan705 · September 15, 2008, 11:24pm

Hi cfajohnson

cfajohnson:


FILE=$1
awk '/^CREATE SET TABLE/ { tablename = $4 }
(/^New_Cd/ && last != "Standardized_Ind") ||
(/^Table_Name/ && last != "^New_Cd" ) { ## Start action here
printf "Error: %s\n", tablename } { last = $1 } ' "$FILE" | uniq


FILE=$1
awk -F  ' /^CREATE SET TABLE/ { tablename = $4 }
(/^New_Cd/ && last != "Standardized_Ind") ||
(/^Table_Name/ && last != "^New_Cd" )
{ printf "Error: %s\n", tablename } { last = $1 } ' "$FILE" | uniq

##

I am still getting the below error message when calling the above script
ab.sh b.txt
Error: XX
Error: XX1
Error: XX2


b.txt
CREATE SET TABLE XX ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Categ_Cd VARCHAR(10) CHARACTER ,
Categ_Desc VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) ) ;
CREATE SET TABLE XX1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Categ_Cd1 VARCHAR(10) ,
Categ_Desc1 VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) ) ;
CREATE SET TABLE XX2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Financial_Asset_Categ_Cd VARCHAR(10) ,
Financial_Asset_Categ_Desc VARCHAR(250) ,
Standardized_Ind CHAR(1) ,
New_Cd VARCHAR(20) ,
Table_Name VARCHAR(100) );

Thanks,
MR

mohan705 · September 15, 2008, 11:31pm

sorry I forgot to remove F option


FILE=$1
awk ' /^CREATE SET TABLE/ { tablename = $4 }
(/^New_Cd/ && last != "Standardized_Ind") ||
(/^Table_Name/ && last != "^New_Cd" )
{ printf "Error: %s\n", tablename } { last = $1 } ' "$FILE" | uniq

Thanks,
MR