Hello, How do I use bind variables for the SQL statements having IN clause. F.ex
SELECT id, name FROM t WHERE id in (10,20,30) As the IN list will have 'n' number of values, I am not able to specify fixed number of bind variables like SELECT id, name FROM t WHERE id in (?,?,?....) As our product supports various other RDBMS I use following solution in case of Oracle SELECT Id, SYSDATE FROM t WHERE Id IN (SELECT * FROM THE (select CAST( fn_vostrtbl (:1) AS voTableType ) FROM dual)) CREATE OR REPLACE TYPE voTableType AS TABLE OF NUMBER; / CREATE OR REPLACE FUNCTION fn_vostrtbl ( p_str in varchar2 ) RETURN voTableType AS l_str LONG DEFAULT p_str || ','; l_n NUMBER; l_data voTableType := voTableType(); BEGIN LOOP l_n := instr( l_str, ',' ); exit when (nvl(l_n,0) = 0); l_data.extend; l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1))); l_str := substr( l_str, l_n+1 ); END LOOP; RETURN l_data; END; / ------------------------------------------------------------- SELECT id, SYSDATE FROM t WHERE id IN ( SELECT * FROM THE ( SELECT CAST( fn_vostrtbl( :1 ) AS VOTABLETYPE ) FROM dual ) ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 13 0.00 0.00 0 0 0 0 Fetch 17 0.00 0.00 0 39 156 91 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 31 0.00 0.00 0 39 156 91 ------------------------------------------------------------- With the use of above code, I can parse SQL statement with variable length IN clause only once and execute it #N number of times. How do I implement the same in Derby. Thanks in advance Sameer Deshpande -- View this message in context: http://www.nabble.com/How-to-use-bind-variables-for-IN-clause-in-SQL-statement-tf3599725.html#a10054807 Sent from the Apache Derby Developers mailing list archive at Nabble.com.
