|
View previous topic :: View next topic
|
| Author |
Message |
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
Hi,
I have coded a SQL stored procedure in which one of the input parameter gets a comma separated values(also in single quotes).
| Code: |
| IN INPARM3 VARCHAR (255) |
the value can be like 'A','B','C'
I use this same value to supply to IN Predicate in static query in the same stored procedure
| Code: |
| select * from example_table where ex_fld in (INPARM3 ); |
so the intention here is to fetch as much rows from the table, in which the ex_fld column can have values like A or B or C etc.,
A query which would be like
| Code: |
| select * from example_table where ex_fld in ('A','B','C' ); |
the problem I am facing here is that , the query considers INPARM3 as a single string made of 'A','B','C' and seaches for a row which has this value ie., ' 'A','B','C' '
I am not sure why this converts into a single string in the static query and I am trying to avoid a work around like parsing the input parm into multiple variables and then using those in the query(which is the only option I can think of ).
any help/pointers here is appreciated.
Thanks in Advance |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
first look at example 4
once upon a time, you could supposedly use
IN :EMPNO-STRUCTURE
but that never did work reliably.
now, to your code. using the varchar field in static sql
at best uses the complete length of the field.
i would suggest that you have a temp table
(if your vsn of db2 support it, use a WITH clause)
and load the substr of the varchar field into the temp table
or WITH(....
and use and EXISTS (SELECT . FROM TEMP/WITH table)
GUYC will be along later, and probably give you a better suggestion.
and stop 'thanks in advance'.
provide thanks afterwards. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
AFAIK there is no way you can do that with static SQL.
You could create a string containing the statement completeley and prepare that.
| Code: |
stmt = 'select * from example_table where ex_fld in (' || inparm || ');'
prepare s1 from stmt |
|
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| You can insert the in parms in a GTT table -- all the values separated by comma in different rows. and use the GTT table in the query. |
|
| Back to top |
|
|
|
|
