Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of BOKKEN@cerner.com designates
 159.140.195.94 as permitted sender)
From: "Okken,Brett" <BOKKEN@CERNER.COM>
To: Derby Discussion <derby-user@db.apache.org>
Subject: RE: tuple IN clause
Thread-Topic: tuple IN clause
Thread-Index: Ac345sB4YFsK5JJ0S5C1YZkoU0+MCwAA1wLgAC8NiYAAAzet8A==
Date: Wed, 23 Jan 2013 19:04:53 +0000
Message-ID: <2042C6313E3E2E47A68B2AE60E650F4CB14FA7@CERNMSGLS5MB1A.cerner.net>
References: <2042C6313E3E2E47A68B2AE60E650F4CB14398@CERNMSGLS5MB1A.cerner.net>
 <50FFF065.6040800@oracle.com>
In-Reply-To: <50FFF065.6040800@oracle.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

The syntax that you are suggesting appears to only work with literal values=
. When I try to use bind variables and get a prepared statement I get:

Exception when calling org.apache.derby.impl.jdbc.EmbedConnection30.prepare=
Statement:
java.sql.SQLException: A table constructor that is not in an INSERT stateme=
nt has all ? parameters in one of its columns.  For each column, at least o=
ne of the rows must have a non-parameter.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown =
Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(U=
nknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unkn=
own Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Sour=
ce)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Sour=
ce)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Sour=
ce)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Sour=
ce)
	at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Sou=
rce)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Sou=
rce)


I am looking at the wiki[1] and cannot determine if I should expect either =
of the tuple syntaxes I was attempting to work. Basically, should I log an =
enhancement or defect?

[1] - http://wiki.apache.org/db-derby/SQLvsDerbyFeatures


Brett Okken | CAMM Platform Services | Lead Architect | 816.201.6112 | www.=
cerner.com | bokken@cerner.com


-----Original Message-----
From: Rick Hillegas [mailto:rick.hillegas@oracle.com]=20
Sent: Wednesday, January 23, 2013 8:15 AM
To: derby-user@db.apache.org
Subject: Re: tuple IN clause

On 1/22/13 1:48 PM, Okken,Brett wrote:
> I need to be able to perform a select statement where the=20
> 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=20
> some other databases I can do something similar, but using the values=20
> keyword:
> select * from table where (auth, identifier) in (values(?,?),(?,?)...)=20
> 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,=20
> in derby I would have to do something like:
> select * from table
> where
> (auth =3D ? and identifier =3D ?)
> OR
> (auth =3D ? and identifier =3D ?)
> OR
> (auth =3D ? and identifier =3D ?)
> ...
> [1] - http://stackoverflow.com/a/8011075/1167722
> <http://stackoverflow.com/a/8011075/1167722>
> Brett Okken | CAMM Platform Services | Lead Architect | 816.201.6112 |=20
> www.cerner.com <http://www.cerner.com> | bokken@cerner.com=20
> <mailto:bokken@cerner.com> CONFIDENTIALITY NOTICE This message and any=20
> included attachments are from Cerner Corporation and are intended only=20
> for the addressee. The information contained in this message is=20
> confidential and may constitute inside or non-public information under=20
> international, federal, or state securities laws. Unauthorized=20
> forwarding, printing, copying, distribution, or use of such=20
> information is strictly prohibited and may be unlawful. If you are not=20
> the addressee, please promptly delete this message and notify the=20
> sender of the delivery error by e-mail or you may call Cerner's=20
> corporate offices in Kansas City, Missouri, U.S.A at (+1)=20
> (816)221-1024.
Hi Brett,

The syntax you want to use looks legal to me, although Derby doesn't suppor=
t it. That's my reading of the SQL Standard, part 2, sections 8.4 <in predi=
cate> and 7.2 <row value expression>. The following script shows how you ca=
n add column names to your values expression:

connect 'jdbc:derby:memory:db;create=3Dtrue';

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 =3D=
 x and u =3D y;

Hope this helps,
-Rick


CONFIDENTIALITY NOTICE This message and any included attachments are from C=
erner 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 in=
formation is strictly prohibited and may be unlawful. If you are not the ad=
dressee, please promptly delete this message and notify the sender of the d=
elivery error by e-mail or you may call Cerner's corporate offices in Kansa=
s City, Missouri, U.S.A at (+1) (816)221-1024.