DB2 EE stage - Extract - SQL IN Clause

[Nageshsunkoji]

Hi All,

My Requirement is like these :

In my SQL I have to use IN condition for checking the few strings. I have to pass these strings in a parameter as per my requirement. I am using the DB2 EE stage for my extract from DB2. But, my job is aborting as it is not able to perform IN function with the strings (I have passed these string in a Parameter). Same IN condition is properly working for Integers if I passed the values in a parametr. Is there any constraint in DB2 EE stage for checking the strings by using IN clause ? Is anybody faced these type of problem ? Please share your experiences.

[ray.wurlod]

Show us precisely what you have done.

Also open the "job starting" event from the job log and show us the actual value used for the parameter.

Chances are you've not gotten the quotes right in the syntax for the IN clause.

[Nageshsunkoji]

Hi Ray,

Thanks for your reply.

I am passing the parameter #FACPARAM# and passing the values as '100A','101B','101C' and inside that SQL I am passing the parameter without Quotations like FAC in (#FACPARAM#). In this Case job is aborting.
If I pass the values in the parameter like 101A,101B,101C and inside SQL, I am passing the parameter with quotations like ('#FACPARAM#'), in this case job is running successfully but its not checking the strings and its treating the entire values 101A,101B,101C as one string and its not giving the result.

Your inputs are valuable to us.

[Nageshsunkoji]

Finally I got the sloution.

We have to give the values as 101A'\,\'101B'\,\'101C in the parameter and inside SQl we have to pass the parameter in Quotations like IN ('#FACPARAM#').

It worked for me.

[ray.wurlod]

That's why I asked you to look in the log for the actual parameter as passed - then I would almost certainly have suggested escaping the characters that are meaningful to the shell. So, well done for solving it.