Prev: love with learning and earning of the people
Next: Alternative to COUNT(*)
From: cjflorian on 15 Feb 2010 03:18
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.
From: Tonkuma on 15 Feb 2010 06:00
What is y1?
From: Tonkuma on 15 Feb 2010 06:13
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.

From: cjflorian on 15 Feb 2010 10:59
That worked. Thanks very much.



On Feb 15, 6:13 am, Tonkuma <tonk...(a)fiberbit.net> wrote:
> 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.

 | 
Pages: 1
Prev: love with learning and earning of the people
Next: Alternative to COUNT(*)