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
 

 

Dynamic sql query based on the number of input

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

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Mon Sep 15, 2008 12:20 pm    Post subject: Dynamic sql query based on the number of input
Reply with quote

i have a stored procedure, which takes userids as input. there can be minimum one or maximum ten userids. i have to write a dynamic sql query based on the number of input i get from front end.

if one userid is given then
Code:
select * from table
where u_id in (:userid1)


if two userids are given then
Code:
select * from table
where u_id in (:userid1, :userid2)


is it possible or i have to write ten separate query. icon_question.gif
Back to top
View user's profile Send private message

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Sep 15, 2008 12:50 pm    Post subject:
Reply with quote

Doesn't it give -811 for the second query? I assume there are rows for each of the userids.

May be you need to specify the requirement.
Back to top
View user's profile Send private message
paritosh mathur

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Mon Sep 15, 2008 1:01 pm    Post subject: dynamic sql query.
Reply with quote

Bharath Bhat wrote:
Doesn't it give -811 for the second query? I assume there are rows for each of the userids.

May be you need to specify the requirement.


no sir it wont give -811, there is only one row for each userid in table.

u_id u_age u_salary
--------------------------
123 23 11111
234 24 22222
345 23 22224

user can give 123 only or 123 and 234 or all the 3 ids. based on this my query shuld be written.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Sep 15, 2008 2:33 pm    Post subject:
Reply with quote

You can give all the 10 expecting userids in your query ...

IN (:userid1, :userid2 ......, :userid10) ... the valuse which you are receiving if say 2 then first 2 will have acutual values and rest all 8 should contain spaces or zeroes... provided spacs or zeroes shud never appear in the table ....
Back to top
View user's profile Send private message
paritosh mathur

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Mon Sep 15, 2008 3:00 pm    Post subject:
Reply with quote

ashimer wrote:
You can give all the 10 expecting userids in your query ...

IN (:userid1, :userid2 ......, :userid10) ... the valuse which you are receiving if say 2 then first 2 will have acutual values and rest all 8 should contain spaces or zeroes... provided spacs or zeroes shud never appear in the table ....


hi ashimer,

i have done like this way only. i wanted to know, can't there be any optmized way for this. ain't there any funda of dynamic sql query.
i may be wrong as well.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Sep 15, 2008 4:30 pm    Post subject:
Reply with quote

definitely you can use dynamic query for this .....
Back to top
View user's profile Send private message
paritosh mathur

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Mon Sep 15, 2008 6:40 pm    Post subject:
Reply with quote

ashimer wrote:
definitely you can use dynamic query for this .....


can u tell me the way..????
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Sep 15, 2008 7:02 pm    Post subject:
Reply with quote

paritosh,

If your input consists of 2 ids ..then

STMT = ' select * from table where id in (:userid1,:userid2) '

prepare and execute this query ....

now depending on your input change your STMT .....
Back to top
View user's profile Send private message
paritosh mathur

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Mon Sep 15, 2008 7:07 pm    Post subject:
Reply with quote

ashimer wrote:
paritosh,

If your input consists of 2 ids ..then

STMT = ' select * from table where id in (:userid1,:userid2) '

prepare and execute this query ....

now depending on your input change your STMT .....


actually my input can have maximum 150 id in a single string. first i will separate all ids. n count how many numbers of id are there. n then i will execute the query based on number of ids..
If i write in above mentioned way then i have to write 750 queries.
did u understand what my problem is..??
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Sep 15, 2008 7:14 pm    Post subject:
Reply with quote

you should have mentioned it earlier .....

create a global temporary table with all the data you want to pass on to FE ....

insert into this table the data for each userid using

INSERT INTO SESSION.TABLE
SELECT * FROM BASETABLE WHERE USERID = :USERID

repeat this for all user ids and then change your FE to fetch data from this table .... or else go for the dynamic query ..
Back to top
View user's profile Send private message
paritosh mathur

New User


Joined: 10 Apr 2006
Posts: 14

PostPosted: Tue Sep 16, 2008 1:44 pm    Post subject:
Reply with quote

ashimer wrote:
you should have mentioned it earlier .....

create a global temporary table with all the data you want to pass on to FE ....

insert into this table the data for each userid using

INSERT INTO SESSION.TABLE
SELECT * FROM BASETABLE WHERE USERID = :USERID

repeat this for all user ids and then change your FE to fetch data from this table .... or else go for the dynamic query ..


thanx a lot.
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 Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Edit large number of datasets (QSAM) zh_lad TSO/ISPF 3 Tue Apr 04, 2017 6:08 pm


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