DSXchange

hi I am trying to pass Job parameter to Query in Odbc Enterprise stage
e.g .


SELECT
Count(ID) As NUMBER_OF_REC
FROM
SDM_PLAN
WHERE
#jpTmpTestFilter# DAY >= to_date('31/12/2005','DD/MM/YYYY')


And jpTmpTestFilter = CUSTOMER_NUMBER IN ('01135101','01200662') AND


it select 0 rows ,But view data show all records

What value are you passing for the parameter or typing in during your SELECT and did you intentionally not put hash marks around the parameter in your SELECT statement?

Umm, Arnd, both the hash marks and the parameter value being passed are there in the post.

Ok, I missed that. I thought the OP had used both in the SELECT statement and that the values (and syntax) didn't match.

My firs thought is that the embedded single quotes in the parameter are not making it to the query correctly. Can you see the parameter value, with single quotes, in the Job Log?

Kind of odd that View Data works but the job doesn't, usually it seems to me to be the other way 'round. Under what circumstances would that happen? Metadata issues?

ArndW wrote:Ok, I missed that. I thought the OP had used both in the SELECT statement and that the values (and syntax) didn't match.

My firs thought is that the embedded single quotes in the parameter are not making it to the query correctly. Can you see the parameter value, with single quotes, in the Job Log?

thanks,
you r correct,
when i remove single quote its works that i found out before ,but my requirement is to to pass it as varchar,when i pass charetor without quote
it gives error
main_program: [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00904: "ABCD": invalid identifier

e.g. job parameter
CUSTOMER_NUMBER IN (01135101,01200662,ABCD) AND

If it's VarChar ALL of the values in the IN list must be quoted in single quotes.

ray.wurlod wrote:If it's VarChar ALL of the values in the IN list must be quoted in single quotes. ...

that i know ,and that single quote is creating problem

e.g. if we pass job parameter valuen as
CUSTOMER_NUMBER IN ('01135101','01200662','ABCD') AND

view data will show records , and when we run job 0 rows get selected

But


if we pass parameter as CUSTOMER_NUMBER IN (01135101,01200662)
i.e. with out single quote
it will give correct output

In this case it works because the number string is implicitly converted to number format. Will the quoting work if you use "\'" instead of "'"?

ArndW wrote:In this case it works because the number string is implicitly converted to number format. Will the quoting work if you use "\'" instead of "'"? ...

That i have tried
but it gives error as Invalid charecter ,even view data is not possible

but i like to confirm ,do u like to say like this
e.g. job parameter
CUSTOMER_NUMBER IN (\'01135101\',\'01200662\',\'ABCD\') AND

Oh well, that approach works for some calls that are passed through the UNIX shell, so I though it might be worth a try.

It's been reported as working in some areas, guess not here. Something else to try is two single quotes for each one:

CUSTOMER_NUMBER IN (''01135101'',''01200662'',''ABCD'') AND

Note they are NOT double-quotes, but rather two single quotes. I guess you could also try a quoted quote and see if that makes it down the ODBC rabbit hole:

CUSTOMER_NUMBER IN ('''01135101''','''01200662''','''ABCD''') AND

Three single quotes for each one.