Oracle-L: RE: vertical serches on a table

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 01 Oct 2003 14:34:33 -0800
Message-ID: <F001.005D1B6F.20031001143433@fatcity.com>

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

RE: vertical serches on a table - how to