From derby-user-return-14860-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue Jan 22 21:48:58 2013 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 4F51BEFEA for ; Tue, 22 Jan 2013 21:48:58 +0000 (UTC) Received: (qmail 70546 invoked by uid 500); 22 Jan 2013 21:48:58 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 70520 invoked by uid 500); 22 Jan 2013 21:48:58 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 70511 invoked by uid 99); 22 Jan 2013 21:48:57 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Jan 2013 21:48:57 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of BOKKEN@cerner.com designates 159.140.193.23 as permitted sender) Received: from [159.140.193.23] (HELO ppls5mail2.cernercloud.com) (159.140.193.23) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Jan 2013 21:48:50 +0000 Received: from CERNMSGLS5HUB2.cerner.net (cernmsgls5hub2.cerner.net [170.71.96.37]) by PPLS5Mail2.cernercloud.com (8.14.5/8.14.5) with ESMTP id r0MLmS7B019252 for ; Tue, 22 Jan 2013 15:48:28 -0600 Received: from CERNMSGLS5MB1A.cerner.net ([169.254.1.14]) by CERNMSGLS5HUB2.cerner.net ([170.71.96.37]) with mapi id 14.02.0318.004; Tue, 22 Jan 2013 15:48:28 -0600 From: "Okken,Brett" To: "derby-user@db.apache.org" Subject: tuple IN clause Thread-Topic: tuple IN clause Thread-Index: Ac345sB4YFsK5JJ0S5C1YZkoU0+MCwAA1wLg Date: Tue, 22 Jan 2013 21:48:28 +0000 Message-ID: <2042C6313E3E2E47A68B2AE60E650F4CB14398@CERNMSGLS5MB1A.cerner.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [39.250.140.25] Content-Type: multipart/alternative; boundary="_000_2042C6313E3E2E47A68B2AE60E650F4CB14398CERNMSGLS5MB1Acer_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.9.8327,1.0.431,0.0.0000 definitions=2013-01-22_07:2013-01-22,2013-01-22,1970-01-01 signatures=0 X-Virus-Checked: Checked by ClamAV on apache.org --_000_2042C6313E3E2E47A68B2AE60E650F4CB14398CERNMSGLS5MB1Acer_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 ke= yword: 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 d= erby 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 Brett Okken | CAMM Platform Services | Lead Architect | 816.201.6112 | www.= cerner.com | bokken@cerner.com 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. --_000_2042C6313E3E2E47A68B2AE60E650F4CB14398CERNMSGLS5MB1Acer_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
I need to be able to perform a select statement where the qualificatio= ns 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 valu= es 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 =3D ? and identifier =3D ?)
OR
(auth =3D ? and identifier =3D ?)
OR
(auth =3D ? and identifier =3D ?)
...
 
[1] - http://stackoverflow.com/a/8011075/1167722
 
Brett Okken | CAMM Platform Services | Lead Architect | 8= 16.201.6112 | www.cerner= .com | <= font face=3D"Arial">bokken@cerner.com
 
 
 
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 K= ansas City, Missouri, U.S.A at (+1) (816)221-1024.
--_000_2042C6313E3E2E47A68B2AE60E650F4CB14398CERNMSGLS5MB1Acer_--