Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / January 2008

Tip: Looking for answers? Try searching our database.

Using Array content in IN expression

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jabernet@swissasp.ch - 08 Jan 2008 18:59 GMT
Is it possible in the new DB2 9.5 to use the content of an array in an
IN expression?

Say we have the ddl:

CREATE TYPE Numbers AS Integer ARRAY[];

CREATE tab1 AS (
 id integer,
 numbers Numbers
);

CREATE tab2 AS (
 id integer
);

Is such SQL possible:

SELECT tab2.* FROM tab2 WHERE tab2.id IN (SELECT numbers FROM tab1
WHERE id = 12)

And are the semantics, that it will match all rows from tab2 where the
id is contained in the numbers array from tab1?

Regards,
Janick
Serge Rielau - 08 Jan 2008 22:41 GMT
> Is it possible in the new DB2 9.5 to use the content of an array in an
> IN expression?
[quoted text clipped - 19 lines]
> And are the semantics, that it will match all rows from tab2 where the
> id is contained in the numbers array from tab1?
IN (SELECT numbers FROM (UNNEST(numberarray)) AS X(numbers)

Now, ARRAYs are supported in stored procedures only.
No column type.

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

db2 - 09 Jan 2008 07:37 GMT
This is a frequently used query.
I think upgrading to 9.5 might need lot of code changes.
Thanks,
Joseph
http://db2examples.googlepages.com
> jaber...@swissasp.ch wrote:
> > Is it possible in the new DB2 9.5 to use the content of an array in an
[quoted text clipped - 34 lines]
>
> - Show quoted text -
jabernet@swissasp.ch - 10 Jan 2008 14:56 GMT
> IN (SELECT numbers FROM (UNNEST(numberarray)) AS X(numbers)
>
[quoted text clipped - 7 lines]
> DB2 Solutions Development
> IBM Toronto Lab

Ok, but I could use this in static SQL like

SELECT * FROM tab1 WHERE col1 IN (SELECT numbers FROM
(UNNEST(:numberarray)) AS X(numbers)

where numbearray would be a host-variable (in java, say)?
Because this would be really cool, as right now, we often pass a set
of ids as a string to a stored procedure and then have to create
dynamic sql like

String sql = "SELECT * FROM tab1 WHERE col1 IN ("+ids+") ";

Regards,
Janick
Serge Rielau - 10 Jan 2008 22:03 GMT
>> IN (SELECT numbers FROM (UNNEST(numberarray)) AS X(numbers)
>>
[quoted text clipped - 4 lines]
> SELECT * FROM tab1 WHERE col1 IN (SELECT numbers FROM
> (UNNEST(:numberarray)) AS X(numbers)
No. Stored procedures and CALL statements for now.
Feel free to submit a feature request.
Generalizing Java support would actually be quite simple I believe...

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

jabernet@swissasp.ch - 10 Jan 2008 22:35 GMT
> jaber...@swissasp.ch wrote:
> >> IN (SELECT numbers FROM (UNNEST(numberarray)) AS X(numbers)
[quoted text clipped - 16 lines]
> DB2 Solutions Development
> IBM Toronto Lab

Thx for the Info. How would I submit a feature request? Through ESR?

Regards,
Janick
Serge Rielau - 11 Jan 2008 12:34 GMT
>> jaber...@swissasp.ch wrote:
>>>> IN (SELECT numbers FROM (UNNEST(numberarray)) AS X(numbers)
[quoted text clipped - 7 lines]
>> Generalizing Java support would actually be quite simple I believe...
> Thx for the Info. How would I submit a feature request? Through ESR?
I don't know the acronym...
Do you have an IBM rep?

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

jabernet@swissasp.ch - 13 Jan 2008 19:17 GMT
> I don't know the acronym...
> Do you have an IBM rep?
[quoted text clipped - 5 lines]
> DB2 Solutions Development
> IBM Toronto Lab

ESR is the support service, where you can create PMRs.

No, I don't think we have any IBM representative (I suppose this is
what rep means? :)).

Regards,
Janick
Serge Rielau - 14 Jan 2008 14:09 GMT
>> I don't know the acronym...
>> Do you have an IBM rep?
> ESR is the support service, where you can create PMRs.
OK. Logging a PMR for a feature request leaves a track record.
Now, please don't expect you get the feature because you alone ask for it.
We'll see if there are more requests coming in and prioritize
accordingly as customers start picking up usage.

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.