Hi.

I would like to retrieve all records from a table except these where
the (a, b) combination has one of a series of specific values. For
example, in MySQL I can given the table:

a b
1 2
1 3
3 4

Do:

SELECT a, b
FROM tbl
WHERE (a, b) != (1,2) AND (a, b) != (3,4)

And get result:

1 3

Whats the DB2 equivalent?

Thanks.

Morten

usenet@kikobu.com wrote:

>
Hi.
>
I would like to retrieve all records from a table except these where
the (a, b) combination has one of a series of specific values. For
example, in MySQL I can given the table:
>
a b
1 2
1 3
3 4
>
Do:
>
SELECT a, b
FROM tbl
WHERE (a, b) != (1,2) AND (a, b) != (3,4)
>
And get result:
>
1 3
>
Whats the DB2 equivalent?

DB2 only supports the equality comparison:
http://publib.boulder.ibm.com/infoce...n/r0000747.htm

So you'll have to simply transform the above predicate.

NOT ( (a, b) = (1, 2) OR (a, b) = (3, 4) )

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Knut Stolze wrote:

usenet@kikobu.com wrote:
>

>
DB2 only supports the equality comparison:
http://publib.boulder.ibm.com/infoce...n/r0000747.htm
>
So you'll have to simply transform the above predicate.
>
NOT ( (a, b) = (1, 2) OR (a, b) = (3, 4) )
>

or use the [NOT] IN predicate
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

usenet@kikobu.com wrote:

Hi.
>
I would like to retrieve all records from a table except these where
the (a, b) combination has one of a series of specific values. For
example, in MySQL I can given the table:
>
a b
1 2
1 3
3 4
>
Do:
>
SELECT a, b
FROM tbl
WHERE (a, b) != (1,2) AND (a, b) != (3,4)
>
And get result:
>
1 3
>
Whats the DB2 equivalent?
>
Thanks.
>
Morten

The DB2 equivalent, actually, the SQL equivalent, is to use IN.

In DB2 style (because of the VALUES clause) it becomes:

SELECT a, b
FROM tbl
WHERE (a, b) NOT IN (VALUES(1,2), (3,4))

B.