Hi Sameer,
You might try using a temporary table to hold the values in the IN list.
For more details, see the section in the Derby Reference Manual titled
"DECLARE GLOBAL TEMPORARY TABLE statement". Here's an example of this
technique.
Regards,
-Rick
autocommit off;
ij> drop table t;
0 rows inserted/updated/deleted
ij> create table t( id int, name varchar(50) );
0 rows inserted/updated/deleted
ij> declare global temporary table session.tempIDs( tmpID int ) not logged;
0 rows inserted/updated/deleted
ij> commit;
ij> insert into t( id, name )
values
( 1, 'foo1' ),
( 10, 'foo10' )
;
2 rows inserted/updated/deleted
ij> commit;
ij> insert into session.tempIDs( tmpID ) values ( 10 ), ( 20 ), ( 30 );
3 rows inserted/updated/deleted
ij> select id, name
from t, session.tempIDs
where id=tmpID;
ID |NAME
--------------------------------------------------------------
10 |foo10
1 row selected
ij> commit;
ij> select id, name
from t, session.tempIDs
where id=tmpID;
ID |NAME
--------------------------------------------------------------
0 rows selected
ij> commit;
Sameer Deshpande wrote:
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
<code>
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
-------------------------------------------------------------
</code>
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
_______________________________________________________________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192
