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.
Strings with Quotes in Oracle Connector-Stage
Moderators: chulett, rschirm, roy
Strings with Quotes in Oracle Connector-Stage
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 251
- Joined: Mon Jun 09, 2008 5:52 am
Hello Srinivas.g,srinivas.g wrote:Use '#WHERE_COND#'
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').
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
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.
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.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
Hello chulett,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.
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.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon