vertical serches on a table - how to

2003-10-01 Thread Susan Tay 
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

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

RE: vertical serches on a table - how to

2003-10-01 Thread Stephane Faroult 
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

2003-10-01 Thread Susan Tay 
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: RE: vertical serches on a table - how to

2003-10-01 Thread Stephane Faroult 
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

2003-10-01 Thread Susan Tay 
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
-
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

2003-10-01 Thread Jamadagni, Rajendra 
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 -- 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).



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

RE: vertical serches on a table - how to

2003-10-01 Thread Khedr, Waleed 
---
-- 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