Hi,
I have a Line input for awk as follows
DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS","COMM_STACK"."UPD_TS" "UPD_TS","COMM_STACK"."WORK_DIM_KEY" "WORK_DIM_KEY","COMM_STACK"."STOCK_DIM_KEY" "STOCK_DIM_KEY","COMM_STACK"."COMM_DIM_KEY" "COMM_DIM_KEY","COMM_STACK"."TRAN_REF_DIM_KEY" "TRAN_REF_DIM_KEY","COMM_STACK"."TRN_ID" "TRN_ID","COMM_STACK"."RESALE_DT" "RESALE_DT","COMM_STACK"."TRAN_CD" "TRAN_CD","COMM_STACK"."POST_DT" "POST_DT","COMM_STACK"."SETTLE_DT" "SETTLE_DT","COMM_STACK"."BRKR_ID" "BRKR_ID","COMM_STACK"."EXEC_BRKR_ID" "EXEC_BRKR_ID","COMM_STACK"."EXEC_TRDR_ID" "EXEC_TRDR_ID","COMM_STACK"."ORD_ID" "ORD_ID","COMM_STACK"."QUANTITY" "QUANTITY","COMM_STACK"."PRICE_LOCAL" "PRICE_LOCAL","COMM_STACK"."PRICE_USD" "PRICE_USD","COMM_STACK"."COMM_AMT_LOCL" "COMM_AMT_LOCL","COMM_STACK"."AMT_USD" "AMT_USD","COMM_STACK"."COMM_AMT_USD" "COMM_AMT_USD","COMM_STACK"."COMM_IMPUTE_USD" "COMM_IMPUTE_USD","COMM_STACK"."NOMINAL_AMT_USD" "NOMINAL_AMT_USD","COMM_STACK"."FACE_VALUE_USD" "FACE_VALUE_USD","COMM_STACK"."FWD_AMT_USD" "FWD_AMT_USD","COMM_STACK"."AMT_LOCL" "AMT_LOCL","COMM_STACK"."EXCH_RT" "EXCH_RT","COMM_STACK"."ACCT_DIM_KEY" "ACCT_DIM_KEY","COMM_STACK"."ORIG_FACE" "ORIG_FACE","COMM_STACK"."INT_AMT_LOCL" "INT_AMT_LOCL","COMM_STACK"."INT_AMT_BASE" "INT_AMT_BASE","COMM_STACK"."ORIG_COST_LOCL" "ORIG_COST_LOCL","COMM_STACK"."ORIG_COST_BASE" "ORIG_COST_BASE","COMM_STACK"."LT_GL_LOCL" "LT_GL_LOCL","COMM_STACK"."LT_GL_BASE" "LT_GL_BASE","COMM_STACK"."ST_GL_LOCL" "ST_GL_LOCL","COMM_STACK"."ST_GL_BASE" "ST_GL_BASE","COMM_STACK"."MISC_FEE" "MISC_FEE","COMM_STACK"."SEC_FEE_LOCL" "SEC_FEE_LOCL","COMM_STACK"."TAXES_LOCL" "TAXES_LOCL","COMM_STACK"."NEW_ELIM_CD" "NEW_ELIM_CD","COMM_STACK"."FIRST_LOT_DT" "FIRST_LOT_DT","COMM_STACK"."TRN_YLD" "TRN_YLD","COMM_STACK"."PERF_MONTH" "PERF_MONTH","COMM_STACK"."BOOK_YIELD" "BOOK_YIELD","COMM_STACK"."BOOK_YIELD_STAT" "BOOK_YIELD_STAT","COMM_STACK"."LT_GL_BASE_STAT" "LT_GL_BASE_STAT","COMM_STACK"."CORP_ACT_RT" "CORP_ACT_RT","COMM_STACK"."SETTLE_LOC" "SETTLE_LOC","COMM_STACK"."CLIENT_QLFD_QUAL" "CLIENT_QLFD_QUAL","COMM_STACK"."BOOK_VALUE_GAAP" "BOOK_VALUE_GAAP","COMM_STACK"."BOOK_VALUE_STAT" "BOOK_VALUE_STAT","COMM_STACK"."BOOK_VALUE_MGR" "BOOK_VALUE_MGR","COMM_STACK"."LT_GL_LOCL_STAT" "LT_GL_LOCL_STAT","COMM_STACK"."COST_BASE" "COST_BASE","COMM_STACK"."COST_LOCL" "COST_LOCL" FROM "MCR"."COMM_STACK"@CL.DIMM.COM "COMM_STACK"
/
Note: When this statement is viewed on a text editor all the statement from the "AS SELECT" to the end of line is displayed in one line.
I would like to have an awk script that looks for all the occurances of the pattern "," (With Quotes) and replace them with the new line character.
I was able to achive this with the following command in awk
(Save the above statement in a mview.sql file and run the below command)
nawk '{gsub("\",","\",\n\t\t", $0); print }' mview.sql | nawk '{gsub(" FROM ","\nFROM ", $0); print }' > mview.out
Output As Shown below (Only Part of the output shown)
DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS",
"COMM_STACK"."UPD_TS" "UPD_TS",
"COMM_STACK"."WORK_DIM_KEY" "WORK_DIM_KEY",
"COMM_STACK"."STOCK_DIM_KEY" "STOCK_DIM_KEY",
"COMM_STACK"."COMM_DIM_KEY" "COMM_DIM_KEY",
"COMM_STACK"."TRAN_REF_DIM_KEY" "TRAN_REF_DIM_KEY",
"COMM_STACK"."TRN_ID" "TRN_ID",
"COMM_STACK"."RESALE_DT" "RESALE_DT",
"COMM_STACK"."TRAN_CD" "TRAN_CD",
"COMM_STACK"."POST_DT" "POST_DT",
"COMM_STACK"."SETTLE_DT" "SETTLE_DT",
"COMM_STACK"."BRKR_ID" "BRKR_ID",
"COMM_STACK"."EXEC_BRKR_ID" "EXEC_BRKR_ID",
"COMM_STACK"."EXEC_TRDR_ID" "EXEC_TRDR_ID",
"COMM_STACK"."ORD_ID" "ORD_ID",
"COMM_STACK"."QUANTITY" "QUANTITY",
"COMM_STACK"."PRICE_LOCAL" "PRICE_LOCAL",
"COMM_STACK"."PRICE_USD" "PRICE_USD",
"COMM_STACK"."COMM_AMT_LOCL" "COMM_AMT_LOCL",
"COMM_STACK"."AMT_USD" "AMT_USD",
"COMM_STACK"."COMM_AMT_USD" "COMM_AMT_USD",
"COMM_STACK"."COMM_IMPUTE_USD" "COMM_IMPUTE_USD",
"COMM_STACK"."NOMINAL_AMT_USD" "NOMINAL_AMT_USD",
"COMM_STACK"."FACE_VALUE_USD" "FACE_VALUE_USD",
"COMM_STACK"."FWD_AMT_USD" "FWD_AMT_USD",
"COMM_STACK"."AMT_LOCL" "AMT_LOCL",
"COMM_STACK"."EXCH_RT" "EXCH_RT",
"COMM_STACK"."ACCT_DIM_KEY" "ACCT_DIM_KEY",
"COMM_STACK"."ORIG_FACE" "ORIG_FACE",
"COMM_STACK"."INT_AMT_LOCL" "INT_AMT_LOCL",
"COMM_STACK"."INT_AMT_BASE" "INT_AMT_BASE",
.
.
.
.
.
FROM "MCR"."COMM_STACK"@CL.DIMM.COM "COMM_STACK"
So Far i am happy with what i have seen.My question now is in the pattern i also have a few of such statements which are already in the next line as shown below
DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS",
"COMM_STACK"."UPD_TS" "UPD_TS",
"COMM_STACK"."WORK_DIM_KEY" "WORK_DIM_KEY",
"COMM_STACK"."STOCK_DIM_KEY" "STOCK_DIM_KEY",
"COMM_STACK"."COMM_DIM_KEY" "COMM_DIM_KEY",
"COMM_STACK"."TRAN_REF_DIM_KEY" "TRAN_REF_DIM_KEY",....
Now when a run the same nawk on such a file here is what happens
DROP MATERIALIZED VIEW MCR.COMM_STACK;
CREATE MATERIALIZED VIEW "MCR"."COMM_STACK"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 7
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "COMM_STACK"."COMM_TS" "COMM_TS",
"COMM\_STACK"."UPD_TS" "UPD_TS",
"COMM\_STACK"."WORK\_DIM_KEY" "WORK\_DIM_KEY",
"COMM\_STACK"."STOCK\_DIM_KEY" "STOCK\_DIM_KEY",
"COMM\_STACK"."COMM\_DIM_KEY" "COMM\_DIM_KEY",
"COMM\_STACK"."TRAN\_REF\_DIM_KEY" "TRAN\_REF\_DIM_KEY",
"COMM\_STACK"."TRN_ID" "TRN_ID",
"COMM\_STACK"."RESALE_DT" "RESALE_DT",
.
.
.
.
.
So how can i avoid that from happening. Basically the question is if there is none after ", then do not substitute a new line just ignore. Go after only those that have a continuious line of such statements without a wrap.
Thanks and Kind Regards,
Rajan