----------------------------------------------------------- -- 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 ----------- 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 _________________________________________________________________ Help protect your PC. Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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).