DSXchange

Hi, I am trying to write a dynamic query in Oracle using Datastage variables that get set at time of job running. I am getting errors from the way I am tying to pass the date into Oracle. I have defined a stage variable: srchQueryDateRange - that will hold the date portion of my query, the variable holds the following:

If dateFormat = 'D8' Then " and TO_CHAR(B.srvc_from_dt,\'yyyymmdd\') = ORCHESTRATE.SRVC_FROM_DT"
else If dateFormat = 'RD8' Then " and TO_CHAR(B.srvc_from_dt,\'yyyymmdd\') >= ORCHESTRATE.SRVC_FROM_DT and TO_CHAR(B.srvc_to_dt,\'yyyymmdd\') <= ORCHESTRATE.SRVC_TO_DT" else ""

My problem seems to be trying to pass the format within a variable. Can someone please tell me what I am doing wrong?

Thanks so much for any help.
Mona

What data type is "srchQueryDateRange " and what is the derivation for it?
the if-then-else construct you posted can't be used as the derivation for the stage variable. Also is "dateFormat" a parameter to your job?

the datatype is varchar and the if-then-else construct is correct why can't it be used for the derivation. The derivation for the variable is " and TO_CHAR(B.srvc_from_dt,\'yyyymmdd\') = ORCHESTRATE.SRVC_FROM_DT"
OR " and TO_CHAR(B.srvc_from_dt,\'yyyymmdd\') >= ORCHESTRATE.SRVC_FROM_DT and TO_CHAR(B.srvc_to_dt,\'yyyymmdd\') <= ORCHESTRATE.SRVC_TO_DT" depending on the value in variable dateFormat

By "stage variable" I assume you mean input parameter, you couldn't possibly pass the stage variable to oracle...

When datastage composes the query, what does the query string have for your variable section? Unless it's adjusted in 8.7, I would expect the single quotes to disappear, in 8.5 we had to use the environment variable APT_OSL_PARAMS_ESC_SQUOTE to keep our quotes within parameters.