DSXchange

Hello,

How can I pass a set of string values into a where clause in a SQL query through a parameter?

For Ex: Select * from employee
where id in ('pEmpId')

I want to pass pEmpId='1A','23','4ED'...etc

I am able to pass a single value, but am unable to pass mutiple values.

Please advise.

Please clarify how you are "passing" this value for the parameter to the job.

I am passing values to the job during run-time through the director. Is there a specific format I need to pass the values into?

When I run the job, I specify..

pEmpId='1A','23','4ED'

So long as the parameter type is "String" then you should be able to pass the list of values as you show, for use in an SQL "IN". Check the parameter type in the job design. You may also need to quote around the list of values at start and finish.

I remember having real problems with this, no matter what I tried it kept dropping the initial quotes. So I would pass in the parameter 'A','B' but it would get passed to the oracle query as A','B

If I put double quotes around it, it would pass it as "'A','B'". I also tried various escape characters and each time the SQL would not form properly.

Can't remember what I did in the end - but it involved not passing the values as a parameter.

Let us know how you go.

Well.. It doesn't work.. It removes the quotes in between..

It reads it as pEmpId in ('1A,23,4ED')

I need it as pEmpId in ('1A','23','4ED')

DataStage is notorious for stripping quotes. You may be able to get it to work by "escaping" them and from what I've seen here, differnet methods work for different folks. So try:
For each one and see if any of them help them to survive.

try using a back slash (\) before the single quote. for example i use it like this:
\'OD\' to specify 'OD'. i hoe this works for you.

Try passing values: (1A, 23, 4ED) to the job parameter: pEmpId and use IN (#pEmpId#) in the WHERE clause.

Hello Arun,

I checked passing multiple values for a single field as a parameter in version 8, it works fine. I do not have version 7.5, so i am not sure of that.

But when u provide multiple values for an SQL statement, the values are internally treated as OR conditions. Instead of providing the values, you can provide the whole condition with OR clauses for the SQL Query as parameter.

Example: Select * from <Table> Where #Parameter#

Pass the value 'pEmpId = 1A OR pEmpId = 23 OR pEmpId = 4ED' for the parameter.

Hope this helps.

Let us know if it works or not.

[quote="dsuser_cai"]try using a back slash (\) before the single quote. for example i use it like this:
\'OD\' to specify 'OD'. i hoe this works for you.[/quote]

Thanks Chulett and dsuser_cai. It worked.

This is how I passed the parameter.

pEmpId=1A\',\'23\',\'4ED

Select * from employee
where id in ('pEmpId')

Generated SQL:
Select * from employee
where id in ('1A','23','4ED')

Thanks for posting what worked out for you. :D

I have also faced same problem in one jobs.

In my development environment i have add the escape charanter in the paramter value. It started working.

But in another environment i have kept same job then there are no rows fetched in the job whenre this multi value variable used. so if i have specified the paramter in same way.

Is escape character is related to an environment setting?

Please give some suggestion