> 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