Passing SQL as job parameter

[tkozlows]

Hi,

I am writing a generic job that will accept SQL statement as one of its parameters. The SQL extracts data from a source table. The job then loads the extracted data into the target.

Oracle --> Xform --> Oracle

No columns are defined - I'm using 'Runtime column propagation'.

The job works fine until I include quoted literals in the SQL that is passed to the job, eg.

SELECT COLUMN1, COLUMN2 FROM SOURCE_TABLE WHERE COLUMN3 = 'CONSTANT'

The quotes around 'CONSTANT' are stripped off so that the Oracle stage that uses it receives:

SELECT COLUMN1, COLUMN2 FROM SOURCE_TABLE WHERE COLUMN3 = CONSTANT

The job subsequently aborts.

Is there a way to ensure that the single quotes are correctly passed to the Oracle stage?

Regards
Thomas

[ray.wurlod]

Try "escaping" the quote characters.

Code: Select all

WHERE COLUMN3 = \'CONSTANT\'

[tkozlows]

Thank you Ray. Works fine.

[chulett]

You could also use the 'new' APT variable that turns off the stripping... but I don't recall exactly what it is called at the moment.

Here you go:

http://www-304.ibm.com/support/docview. ... wg21386517