|
|
How to search for right word using awk?
01-21-2014
REPLACE VIEW TABLE1
(
ID
,COL1
,COL2
,COL3
,COL4
,COL5
,COL6
,COL7
)
AS LOCKING ROW FOR ACCESS
SELECT
ID
CASE WHEN COL1 = 'Y' THEN '1' ELSE COL1 END,
CASE WHEN COL2 = 'Y' THEN '1' ELSE COL2 END ,
CASE WHEN COL3 = 'Y' THEN '1' ELSE COL3 END,
CASE WHEN COL4 = 'Y' THEN '1' ELSE COL4
END,
CASE WHEN COL5 = 'Y' THEN '1' ELSE COL5 END
(TITLE 'Field 5')
,CASE WHEN COL6 = 'Y' THEN '1' ELSE COL6 END (TITLE 'Field 6'),
CASE WHEN COL7 = 'Y' THEN '1' ELSE COL7 END (TITLE 'Field 7')
FROM TABLE_ABC
;DDL_NAME=TABLE1.DDL
DDL_VIEW=TABLE1
cat ${DDL_NAME} |
awk '{ if( $0 ~ /\./ ) { print toupper($0) } else print $0 }' |
sed -n "/${DDL_VIEW}/,/;/p" |
sed -e 's/Select/SELECT/g' \
-e 's/select/SELECT/g' \
-e 's/From/FROM/g' \
-e 's/from/FROM/g' |
sed -n '/SELECT/,/FROM /p' |
sed -n '/SELECT/,/FROM$/p' |
sed -e 's/SELECT//g' \
-e '/FROM /,$d' \
-e '/FROM$/,$d' |
tr -d "\t" |
sed -e "s/^ *//g" \
-e "s/ *$//g" \
-e "/^ *$/d" \
-e 's/^,//' \
-e 's/,$//' |
awk '
{
if ( $0 ~ /CASE[ \t]/ && $0 !~ /END[\t]*$/ )
{
caseline=$0
for (;;)
{
getline currline
caseline=caseline" "currline
if ( currline ~ /END[\t]*$/ )
{
print caseline
break
}
}
}
else
{
print $0
}
}'ID CASE WHEN COL1 = 'Y' THEN '1' ELSE COL1 END CASE WHEN COL2 = 'Y' THEN '1' ELSE COL2 END CASE WHEN COL3 = 'Y' THEN '1' ELSE COL3 END CASE WHEN COL4 = 'Y' THEN '1' ELSE COL4 END CASE WHEN COL5 = 'Y' THEN '1' ELSE COL5 END (TITLE 'Field 5') CASE WHEN COL6 = 'Y' THEN '1' ELSE COL6 END (TITLE 'Field 6') CASE WHEN COL7 = 'Y' THEN '1' ELSE COL7 END (TITLE 'Field 7')
ID CASE WHEN COL1 = 'Y' THEN '1' ELSE COL1 END CASE WHEN COL2 = 'Y' THEN '1' ELSE COL2 END CASE WHEN COL3 = 'Y' THEN '1' ELSE COL3 END CASE WHEN COL4 = 'Y' THEN '1' ELSE COL4 END CASE WHEN COL5 = 'Y' THEN '1' ELSE COL5 END (TITLE 'Field 5')
should it look for comma to format a line? and we don't have gawk.|
|