-----------------------------------------------------------
-- 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).
