db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: tuple IN clause
Date Wed, 23 Jan 2013 14:15:01 GMT
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> | 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.
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




Mime
View raw message