From oracle-l-bounce@freelists.org Mon Oct 10 11:15:21 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9AGFLS9027932 for ; Mon, 10 Oct 2005 11:15:21 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9AGFIvX027918 for ; Mon, 10 Oct 2005 11:15:18 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E5D231FA4C7; Mon, 10 Oct 2005 11:15:14 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04074-02; Mon, 10 Oct 2005 11:15:14 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5E3BA1FA56E; Mon, 10 Oct 2005 11:15:14 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=lN0nNoDLncMxyCzlTMCuHOvSM4MJzcs0jPX4D3q81g8NK9v9MJgEoAynNQ48rOqWVnHgF4B95/2ZNk/4CwBOR/H1yXB7NhGFfv5pXBZFdCRk9j0wyHXMJfq9sbeeeBieVav4V3C8ym6Cx6AEVh1LH5bIKe0gSR9O2CttnslKu8c= Message-ID: <9177895d0510100913v27e5e817w2aacf714d8b7aeac@mail.gmail.com> Date: Mon, 10 Oct 2005 12:13:14 -0400 From: rjamya To: dubey.sandeep@gmail.com Subject: Re: SQL help Cc: oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_30515_7329551.1128960794518" References: X-archive-position: 26643 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rjamya@gmail.com Precedence: normal Reply-To: rjamya@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.0 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 ------=_Part_30515_7329551.1128960794518 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline with x as (select length(replace(translate('&1','1234567890',' '),' '))+1 c= 1 from dual) ,y as (select count(*) c2 from t where id in (&1)) select t.* from t, x a, y b where id in (&1) and a.c1 =3D b.c2 / Raj On 10/10/05, Sandeep Dubey wrote: > > Hi, > > I am having weekend hangover with seemingly simple sql requirement. > > create table t(id number); > insert into t values(1); > insert into t values(2); > commit; > > I want to query this with an Id set. All values in the set should be > there to return me any row. > e.g. > select * from t where id in (1,2); return 1 and 2 > > If am serching for 1,2,3 if any one value is missing I should not get any > data. > e.g. > select * from t where id in (1,2,3) should not return any row. > How to rewrite the above query with (1,2,3) that should not return me any > row. > Thanks > > Sandeep > -- > http://www.freelists.org/webpage/oracle-l > -- ------------------------------ select standard_disclaimer from company_requirements where category =3D 'MANDATORY'; ------=_Part_30515_7329551.1128960794518 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
with x as (select length(replace(translate('&1','1234567890',' = ;          '),' '))+1 c1 from = dual)
    ,y as (select count(*) c2 from t where id in (&= amp;1))
select t.*
  from t, x a, y b
 where id in (&= ;1)
   and a.c1 =3D b.c2
/


Raj

On 10/10/05, Sandeep Dube= y <dubey.sandeep@gmail.co= m> wrote:
Hi,
I am having weekend hangover with seemingly simple sql requirement.
create table t(id number);
insert into t values(1);
insert into t val= ues(2);
commit;

I want to query this with an Id set. All values i= n the set should be
there to return me any row.
e.g.
select * from= t where id in (1,2);  return 1 and 2

If am serching for 1,2,3 if any one value is missing I should not g= et any data.
e.g.
select * from t where id in (1,2,3) should not retu= rn any row.
How to rewrite the above query with (1,2,3) that should not = return me any row.
Thanks

Sandeep
--
http://www.freelists.org/webpage/oracle-l



--
------------------------------
s= elect standard_disclaimer from company_requirements where category =3D 'MAN= DATORY';
------=_Part_30515_7329551.1128960794518-- -- http://www.freelists.org/webpage/oracle-l