| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: vertical serches on a table - how to
Susan,
me thinks following condition specified is vague ... when compared to the output you specify.
(COL1=mango and COL2=banana AND COL1=grape and COL2=pineapple)
Raj
-----Original Message-----
Sent: Wednesday, October 01, 2003 10:45 AM
To: Multiple recipients of list ORACLE-L
Stephane,
Apologize for not being clear on my question.
The query you have provided will only return one record, ie. 1 mango banana.
I need two records to be returned:
1 mango banana
1 grape pineapple
You're right that by 'vertical', I meant filtering according to conditions on OTHER rows but at the same time, I want those filter conditions to be displayed as well, which in this case - grape and pineapple.
Any idea.
Thanks.
susan
>From: "Stephane Faroult" <sfaroult_at_oriolecorp.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: vertical serches on a table - how to
>Date: Wed, 01 Oct 2003 06:14:32 -0800
>
> >Hi,
> >I have a table that I would like to perform a
> >vertical search on. For eg.
> >
> >Table X
> >-----------
> >ID COL1 COL2
> >1 apple orange
> >1 mango banana
> >1 grape pineapple
> >2 mango banana
> >2 guava lemon
> >
> >
> >I would like to display records that meet the
> >following criteria for *a
> >particular ID*.
> >
> >(COL1=banana)
> > OR
> >(COL1=mango and COL2=banana
> >AND
> >COL1=grape and COL2=pineapple
> >
> >
> >The output should be
> >ID COL1 COL2
> >1 mango banana
> >1 grape pineapple
> >
> >It should not display
> >ID COL1 COL2
> >2 mango banana
> >
> >since ID=2 did not meet the criteria where
> >COL1=grape and COL2=pineapple.
> >
> >
> >I tried the following SQL but the output is always
> >zero because COL1 can
> >never be a mango and a grape and COL2 can never be
> >a
> >banana and a pineapple at the same time for a
> >particular ID.
> >
> >select ID, col1, col2
> >from tableX
> >where (col1='banana')
> >or ((col1='mango' and col2='banana')
> > and
> > (col1='grape' and col2='pineapple')
> > );
> >
> >
> >Any idea how I can do a vertical search on the
> >table.
> >
> >Thanks for any help you can provide.
> >
> >susan
> >
>
>Susan,
>
> Took me some time to understand what you meant by 'vertical search'.
>ANDs and ORs in a WHERE clause always apply to the current row under
>scrutiny. What you mean by 'vertical' is that you want to filter according
>to conditions on OTHER rows. This is done by a subquery.
>
>Your query could read
>
>select X1.ID, X1.col1, X1.col2
>from tableX X1
>where (X1.col1='banana')
>or ((X1.col1='mango' and X1.col2='banana')
> and EXISTS (select null
> from tableX X2
> where X2.col1='grape'
> and X2.col2='pineapple'
> and X2.ID = X1.ID));
>
>Simplifying to the extreme, each different row you handle must be returned
>by its 'own' query.
>
>Regards,
>
>Stephane Faroult
>Oriole
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Stephane Faroult
> INET: sfaroult_at_oriolecorp.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Susan Tay INET: susantay2_at_hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Oct 01 2003 - 09:55:15 CDT-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
- text/plain attachment: ESPN_Disclaimer.txt
 |
 |