|
View previous topic :: View next topic
|
| Author |
Message |
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
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.  |
|
| Back to top |
|
 |
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
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 |
|
|
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
| 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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
| 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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| definitely you can use dynamic query for this ..... |
|
| Back to top |
|
|
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
| ashimer wrote: |
| definitely you can use dynamic query for this ..... |
can u tell me the way..???? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
| 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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
paritosh mathur
New User
Joined: 10 Apr 2006
Posts: 14
|
|
|
|
| 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 |
|
|
|
|
