"... WHERE (a, b) != (1,2) AND (a,b) != (1,3) ..."

[usenet]

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

[Knut Stolze]

us****@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

[Brian Tkatch]

us****@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.

[Serge Rielau]

Knut Stolze wrote:

us****@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) )

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/