db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Okken,Brett" <BOK...@CERNER.COM>
Subject tuple IN clause
Date Tue, 22 Jan 2013 21:23:44 GMT
I need to be able to perform a select statement where the qualifications is a list of tuples.

For example, assume I have a table:
id (number, primary key)
auth (varchar)
identifier (varchar)

The combination of auth and identifier is unique.
I need to be able to query for a list of auth/identifier combinations.

In oracle I can do the following:
select * from table where (auth, identifier) in ((?,?),(?,?)...)

In some other databases I can do something similar, but using the values keyword:
select * from table where (auth, identifier) in (values(?,?),(?,?)...)
There are at least some[1] who consider this valid sql-92 syntax.

Both of these fail in derby (with a syntax error). As best I can tell, in derby I would have
to do something like:

select * from table
where
(auth = ? and identifier = ?)
OR
(auth = ? and identifier = ?)
OR
(auth = ? and identifier = ?)
...

[1] - http://stackoverflow.com/a/8011075/1167722

Brett Okken | CAMM Platform Services | Lead Architect | 816.201.6112 | www.cerner.com<http://www.cerner.com>
| bokken@cerner.com<mailto:bokken@cerner.com>



CONFIDENTIALITY NOTICE This message and any included attachments are from Cerner Corporation
and are intended only for the addressee. The information contained in this message is confidential
and may constitute inside or non-public information under international, federal, or state
securities laws. Unauthorized forwarding, printing, copying, distribution, or use of such
information is strictly prohibited and may be unlawful. If you are not the addressee, please
promptly delete this message and notify the sender of the delivery error by e-mail or you
may call Cerner's corporate offices in Kansas City, Missouri, U.S.A at (+1) (816)221-1024.

Mime
View raw message