I have coded a SQL stored procedure in which one of the input parameter gets a comma separated values(also in single quotes).
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
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
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.
once upon a time, you could supposedly use
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
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.