Re: PL/SQL variable number of ORs in SELECT
From: Slava Natapov <SNatapov_at_johnbryce.co.il>
Date: 1997/08/07
Message-ID: <33E9FD38.7D78_at_johnbryce.co.il>#1/1
--I have a stored procedure that must build cursors based on an input
--string that contains a variable number of search parameters that are
--ORed together in a select statement.
--For instance if I pass in the string 22;34;67 I would have the
--following statement
--OPEN cursor_name FOR SELECT * FROM my_table WHERE my_num = '22'
Regards
Slava.
Date: 1997/08/07
Message-ID: <33E9FD38.7D78_at_johnbryce.co.il>#1/1
/***************************
--I have a stored procedure that must build cursors based on an input
--string that contains a variable number of search parameters that are
--ORed together in a select statement.
--For instance if I pass in the string 22;34;67 I would have the
--following statement
--OPEN cursor_name FOR SELECT * FROM my_table WHERE my_num = '22'
- OR my_num = '34' OR my_num = '67';
--
--Can I build this dynamically, in some instances we may have to pass in
up
--to 100 search parameters. I looked at the DBMS_SQL package but was
unable
--to find a way to use it in this case.
***************************/ 1. If You want to use DBMS_SQL, You may first to change ";" in your string to "," For example
str:='22;34;67';
select replace(str,';',',')
into str
from dual;
Now You can prepare statemnt for SQL_DBMS
stmnt:= 'SELECT * FROM my_table WHERE my_num in (' ||str|| ')';
2. But You can do the same without dynamic SQL.
Look at this SQL statement:
SELECT * FROM my_table
WHERE my_num =
decode(sign(instr('22;33;67',to_char(my_num))),1,my_num,-1);
Regards
Slava.
snatapov_at_johnbryce.co.il Received on Thu Aug 07 1997 - 00:00:00 CEST