Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Using IN predicate in a SQL stored procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Tue Sep 11, 2012 10:56 am    Post subject: Using IN predicate in a SQL stored procedure
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Sep 11, 2012 12:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Sep 11, 2012 1:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Sep 12, 2012 11:41 am    Post subject: Reply to: Using IN predicate in a SQL stored procedure
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 7 Fri May 19, 2017 9:35 pm
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us