RE: vertical serches on a table - how to
>Hi, >I have a table that I would like to perform a >vertical search on. For eg. > >Table X >--- >IDCOL1 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 COL1COL2 >1mango banana >1grapepineapple > >It should not display >ID COL1COL2 >2mango 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 fromtableX 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: vertical serches on a table - how to
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 mangobanana 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" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 COL1COL2 >1mango banana >1grapepineapple > >It should not display >ID COL1COL2 >2mango 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 >fromtableX >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 fromtableX 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Frustrated with dial-up? Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Susan Tay INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: vertical serches on a table - how to
Title: 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- From: Susan Tay [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 01, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: RE: vertical serches on a table - how to 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" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >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: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] (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). _ Frustrated with dial-up? Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Susan Tay INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California
RE: vertical serches on a table - how to
--- -- There are two rules -- Rule 1 has one test -- Rule 2 has two tests that should be evaluated to TRUE --- drop table test_vertical; create table test_vertical (m_id number,col1 varchar2(100), col2 varchar2(100)); insert into test_vertical values (1 ,'apple', 'orange'); insert into test_vertical values (1 ,'mango', 'banana'); insert into test_vertical values (1 ,'grape', 'pineapple'); insert into test_vertical values (2 ,'mango', 'banana'); insert into test_vertical values (2 ,'guava', 'lemon'); -- This tells you which rule and test was passed by each id select m_id, case when col1 = 'banana' then 1 -- test 1 in rule 1 when col1 = 'mango' and col2 = 'banana' then 1 -- test 1 in rule 2 when col1 = 'grape' and col2 = 'pineapple' then 2 -- test 2 in rule 2 else null end as test_num, case when col1 = 'banana' then 1 -- rule 1 when col1 = 'mango' and col2 = 'banana' then 2 -- rule 2 when col1 = 'grape' and col2 = 'pineapple' then 2 -- rule 2 else null end as rule_num from test_vertical; -- -- Could be used to filter id's that don't meet the conditions -- select m_id, rule_num from (select m_id, case when col1 = 'banana' then 1 -- test 1 in rule 1 when col1 = 'mango' and col2 = 'banana' then 1 -- test 1 in rule 2 when col1 = 'grape' and col2 = 'pineapple' then 2 -- test 2 in rule 2 else null end as test_num, case when col1 = 'banana' then 1 -- rule 1 when col1 = 'mango' and col2 = 'banana' then 2 -- rule 2 when col1 = 'grape' and col2 = 'pineapple' then 2 -- rule 2 else null end as rule_num from test_vertical) group by m_id, rule_num having count(distinct test_num) = decode(rule_num,1,1,2,2,null); - -- Now we can get the data - select * from test_vertical where (m_id,case when col1 = 'banana' then 1 -- rule 1 when col1 = 'mango' and col2 = 'banana' then 2 -- rule 2 when col1 = 'grape' and col2 = 'pineapple' then 2 -- rule 2 else null end) in ( select m_id, rule_num from (select m_id, case when col1 = 'banana' then 1 -- test 1 in rule 1 when col1 = 'mango' and col2 = 'banana' then 1 -- test 1 in rule 2 when col1 = 'grape' and col2 = 'pineapple' then 2 -- test 2 in rule 2 else null end as test_num, case when col1 = 'banana' then 1 -- rule 1 when col1 = 'mango' and col2 = 'banana' then 2 -- rule 2 when col1 = 'grape' and col2 = 'pineapple' then 2 -- rule 2 else null end as rule_num from test_vertical) group by m_id, rule_num having count(distinct test_num) = decode(rule_num,1,1,2,2,null)) Regards, Waleed -Original Message- Sent: Wednesday, October 01, 2003 9:29 AM To: Multiple recipients of list ORACLE-L Hi, I have a table that I would like to perform a vertical search on. For eg. Table X --- IDCOL1 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 COL1COL2 1mango banana 1grapepineapple It should not display ID COL1COL2 2mango 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 banan
RE: RE: vertical serches on a table - how to
>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 mangobanana >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 > In that case you keep something looking vaguely like the condition in your initial query but add a subquery to check that both conditions are satisfied : select X1.ID, X1.col1, X1.col2 from tableX X1 where (X1.col1='banana') or (((X1.col1='mango' and X1.col2='banana') or (X1.col1='grape' and X1.col2='pineapple')) and 2 = (select count(*) from tableX X2 where ((X2.col1='mango' and X2.col2='banana') or (X2.col1='grape' and X2.col2='pineapple')) and X2.ID = X1.ID))); No guarantee on the proper number of parentheses. SF >> >> >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 COL1COL2 >> >1mango banana >> >1grapepineapple >> > >> >It should not display >> >ID COL1COL2 >> >2mango 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: RE: vertical serches on a table - how to
Stephane, Fantastic! Appreciate the help. Thanks! susan From: "Stephane Faroult" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: RE: vertical serches on a table - how to Date: Wed, 01 Oct 2003 08:04:31 -0800 >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 mangobanana >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 > In that case you keep something looking vaguely like the condition in your initial query but add a subquery to check that both conditions are satisfied : select X1.ID, X1.col1, X1.col2 from tableX X1 where (X1.col1='banana') or (((X1.col1='mango' and X1.col2='banana') or (X1.col1='grape' and X1.col2='pineapple')) and 2 = (select count(*) from tableX X2 where ((X2.col1='mango' and X2.col2='banana') or (X2.col1='grape' and X2.col2='pineapple')) and X2.ID = X1.ID))); No guarantee on the proper number of parentheses. SF >> >> >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 COL1COL2 >> >1 mango banana >> >1 grapepineapple >> > >> >It should not display >> >ID COL1COL2 >> >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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services ---
