On 1/22/13 1:48 PM, Okken,Brett wrote:
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
<http://stackoverflow.com/a/8011075/1167722>
Brett Okken | CAMM Platform Services | Lead Architect | 816.201.6112 |
www.cerner.com <http://www.cerner.com> | [email protected]
<mailto:[email protected]>
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.
Hi Brett,
The syntax you want to use looks legal to me, although Derby doesn't
support it. That's my reading of the SQL Standard, part 2, sections 8.4
<in predicate> and 7.2 <row value expression>. The following script
shows how you can add column names to your values expression:
connect 'jdbc:derby:memory:db;create=true';
create table t( d int, u varchar( 30 ) );
insert into t values ( 1, 'abc' ), ( 2, 'def' ), ( 3, 'abc' ), ( 4, 'def' );
select * from t, ( values( 1, 'abc' ), ( 2, 'def' ) ) s( x, y )
where d = x and u = y;
Hope this helps,
-Rick
