Single Quote in Routine

[Munish]

Hi All,

In my routine (Basic) I am writing a sql script.
I am unable to get Single Quote

Routine Syntax

SqlScript = "SET HEADING OFF\n"
SqlScript = SqlScript:"Delete from ":TABLENAME:" Where ETL_KEY in\n"
SqlScript = SqlScript:"(Select ETL_KEY from OLB_ETL_LOAD_FACT where BATCH_ID in\n"
SqlScript = SqlScript:"(Select BATCH_ID from OLB_ETL_BATCH_FACT\n"
SqlScript = SqlScript:"where STATUS <> ":"COMPLETED":" ));\n"
SqlScript = SqlScript:" EXIT;"

The result:

SET HEADING OFF
Delete from EDX_RPT_SERVICE_DETAIL_FACT Where ETL_KEY in
(Select ETL_KEY from OLB_ETL_LOAD_FACT where BATCH_ID in
(Select BATCH_ID from OLB_ETL_BATCH_FACT
where STATUS <> COMPLETED ));
EXIT;

What I want to get is

SET HEADING OFF
Delete from EDX_RPT_SERVICE_DETAIL_FACT Where ETL_KEY in
(Select ETL_KEY from OLB_ETL_LOAD_FACT where BATCH_ID in
(Select BATCH_ID from OLB_ETL_BATCH_FACT
where STATUS <> 'COMPLETED' ));
EXIT;

How to improve my routine (Basic) to get that???

Thanks in advance.

Regards,
Munish

[Cr.Cezon]

try with
SqlScript = "SET HEADING OFF\n"
SqlScript = SqlScript:"Delete from ":TABLENAME:" Where ETL_KEY in\n"
SqlScript = SqlScript:"(Select ETL_KEY from OLB_ETL_LOAD_FACT where BATCH_ID in\n"
SqlScript = SqlScript:"(Select BATCH_ID from OLB_ETL_BATCH_FACT\n"
SqlScript = SqlScript:"where STATUS <> '":"COMPLETED":"' ));\n"
SqlScript = SqlScript:" EXIT;"

[chulett]

In other words, put the single quote inside the surrounding double-quoted strings. Or even simpler, stop concatenating the status as a separate piece as that just complicates your work for (apparently) no gain:

Code: Select all

SqlScript = "SET HEADING OFF\n" 
SqlScript = SqlScript:"Delete from ":TABLENAME:" Where ETL_KEY in\n" 
SqlScript = SqlScript:"(Select ETL_KEY from OLB_ETL_LOAD_FACT where BATCH_ID in\n" 
SqlScript = SqlScript:"(Select BATCH_ID from OLB_ETL_BATCH_FACT\n" 
SqlScript = SqlScript:"where STATUS <> 'COMPLETED' ));\n" 
SqlScript = SqlScript:" EXIT;"