db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: How to use bind variables with IN clause in SQL statement
Date Wed, 18 Apr 2007 13:13:06 GMT
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
>
>   


Mime
View raw message