using composite keys in an IN clause

#1

Hello, using DB2 version 9.7 I assumed the following would work. In
this example, tableA has a composite key on x,y.

select * from tableA where (x, y) IN ( ( 'x1', y1) )

but, DB2 throws this:

SQL0104N An unexpected token "," was found following ", y) in
( ('x1'".
Expected tokens may include: "+". SQLSTATE=42601

Is this not supported in DB2 or is my syntax wrong?

Thanks.

#2

Use VALUES, like this ....
------------------------------ Commands Entered
------------------------------
WITH tableA(x,y) AS(
VALUES ('x1' , 1)
)
select * from tableA where (x, y) IN (VALUES ( 'x1', 1) )
;
------------------------------------------------------------------------------

X Y
-- -----------
x1 1

1 record(s) selected.

Because, right side of multiple values IN predicate should be
(fullselect).
values-clause make a fullselect.

#3

That worked. Thanks very much.

On Feb 15, 6:13*am, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:

Quote:

Use VALUES, like this ....
------------------------------ Commands Entered
------------------------------
WITH tableA(x,y) AS(
VALUES ('x1' , 1)
)
select * from tableA where (x, y) IN (VALUES ( 'x1', 1) )
;
------------------------------------------------------------------------------

X *Y
-- -----------
x1 * * * * * 1

* 1 record(s) selected.

Because, right side of multiple values IN predicate should be
(fullselect).
values-clause make a fullselect.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

using composite keys in an IN clause

comp.databases.ibm-db2 comp.databases.ibm-db2

using composite keys in an IN clause - 02-15-2010 , 02:18 AM

Re: using composite keys in an IN clause - 02-15-2010 , 05:13 AM

Re: using composite keys in an IN clause - 02-15-2010 , 09:59 AM