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.prepareStatement:
java.sql.SQLException: A table constructor that is not in an INSERT statement
has all ? parameters in one of its columns. For each column, at least one 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(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
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 | [email protected]
-----Original Message-----
From: Rick Hillegas [mailto:[email protected]]
Sent: Wednesday, January 23, 2013 8:15 AM
To: [email protected]
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
> 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
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.
