| Passing Variable in IN clause [message #370121] |
Mon, 18 December 2000 13:41  |
Haleem Jamaludeen
Messages: 1
Registered: December 2000
|
Junior Member |
|
|
This is Haleem from Los Angeles.
1 declare
2 myids varchar2(2000) := '204,201,200,253,224';
3 cursor c1 is
4 select empno, ename from emp
5 where empno in (myids)
6 order by ename;
7 begin
8 for rec in c1
9 loop
10 dbms_output.put_line('Emp Numner = '||rec.empno||' Name ='||rec.ename);
11 end loop;
12* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4
ORA-06512: at line 8
This gives error because IN condition doesn't support variable which has more than one values. How do I order by ? Could you help me?
Thanks in Advance,
Haleem
|
|
|
|
| Re: Passing Variable in IN clause [message #370173 is a reply to message #370121] |
Wed, 27 December 2000 11:09  |
A. Roy
Messages: 3
Registered: December 2000
|
Junior Member |
|
|
Hey Haleem,
There are a few approaches to this problem.
The easiest way I can think of would be to make a table which contains all your ID's, and simply join to that table in your Select statement.
You could also hardcode those values in your select statement.
The most complicated solution is to create dynamic SQL (build the SQL on the fly and store it in a string), then execute it. I think option 1 or 2 would be your best bet.
Lots of luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
