DSXchange

In a PX-Job using Runtime-Column-Propagation I try to use an SQL-Statement like this:

Select * from #SOURCETAB# #WHERE_COND#

where #SOURCETAB# is the name of the selected table and #WHERE_COND# is an optional Where-Clause which may contain strings in quotation-marks.

When running the job with #WHERE_COND# = WHERE BWKEY = '0022' the job returns:
Oracle_Enterprise_15,0: (aptoci.C:460). Message: ORA-01722: invalid number.

Obviously the quotation-marks are eliminated when running the job.
My guess was, that it may be necessary to mask the quotation-marks whithin the strings, so I tried to double the quotation-marks.
The result, however, is still the same. It looks like DataStage simply removes all quotation-marks from within the parameter-string.

Does anybody know the correct way to pass the parameter-string?

Any suggestions welcome.

Use '#WHERE_COND#'

srinivas.g wrote:Use '#WHERE_COND#'

Hello Srinivas.g,

nice suggestion, but this would put the whole where-clause into quotation-marks. The result is an invalid SQL-Statement:

Select * from <<TABLE_NAME>> 'where <<COLUMN_NAME>> = <<VALUE>>';

I want to generate:
select * from <TABLE_NAME> where <<COLUMN_NAME>> = '<<VALUE>>'

TABLE_NAME and the complete WHERE-Clause are to be passed as parameters. The Where-Clause may also take the form of an in-list (WHERE x in ('A', 'B', 'C').

There is a workaround to passing the WHERE-Clause as a parameter:
I can generate the complete SQL-Statement, write it to file and execute the SQL from file at runtime. Still I would rather pass the Where-Clause to the job as a parameter, because then I can see it in the log of the job instead of having to go back to the file on the Linux-Server to see the SQL that was executed.

Escape the quotes. What actually works seems to vary with people and their specific version, but the first thing I'd try would be the standard "\" if "quoting the quotes" didn't work for you.

chulett wrote:Escape the quotes. What actually works seems to vary with people and their specific version, but the first thing I'd try would be the standard "" if "quoting the quotes" didn't work for you.

Could you please explain a bit more how did you achieve it?

chulett wrote:Escape the quotes. What actually works seems to vary with people and their specific version, but the first thing I'd try would be the standard "" if "quoting the quotes" didn't work for you.

Hello chulett,

Thanks a lot. Of course the UNIX/Linux-Escape-Character is a good bet when working with an application-server on a UNIX/Linux-platform.

Just for the record:
Within #WHERE_COND# all "'"-characters (Quotation marks) have to be preceded by "" --> "\'".

Works just fine.