Passing string values into SQL query via parameter
Moderators: chulett, rschirm, roy
Passing string values into SQL query via parameter
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.
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.
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.
Mark Winter
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
<i>Nothing appeases a troubled mind more than <b>good</b> music</i>
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.
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.
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.
Code: Select all
\'
'' (two singles)
''' (three singles)
"'" (double single double)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
Re: Passing string values into SQL query via parameter
Try passing values: (1A, 23, 4ED) to the job parameter: pEmpId and use IN (#pEmpId#) in the WHERE clause.
Re: Passing string values into SQL query via parameter
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.
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.
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
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
Thanks & Regards,
Shobhit Jauhari
Shobhit Jauhari