Filtering out Multiple Data From a Field

[getjbb]

Hello

How can I select rows having certain values in a field. For example
if I have the following row:

OBJ NUM
--- ---

AB 23
AB 24
AB 25
AC 26
AD 22
AD 23
AD 24
AD 25
AE 99
AE 20
AE 21


How can I only select the OBJ which have NUM(s): 23 and 24 and 25.

I tried using an ALL operator but that did not work.

The SQL I used to arrive at the above listing is:

SELECT DISTINCT OBJ, NUM
FROM OBJECTS
GROUP BY OBJ, NUM
HAVING OBJ_ON_HAND > 0
ORDER BY OBJ;

I tried using an ALL operator but that did not work:

SELECT DISTINCT OBJ, NUM
FROM OBJECTS
GROUP BY OBJ, NUM
HAVING OBJ_ON_HAND > 0
AND NUM = ALL(2003,2004,2005)
ORDER BY OBJ;

Thanks.

 

[getjbb]

The code I have highlighted in red should be
AND NUM = ALL(23,24,25)

 

[Turkbear]

Hi,
( May be time to brush up on your SqlPlus syntax)

SELECT   DISTINCT OBJ, NUM
FROM     OBJECTS
WHERE NUM IN (23,24,25) and
OBJ_ON_HAND > 0

The Group By and Having clauses are not needed with your posted code as no aggregate functions are used.
If you want to Count the Number of Each NUM within each OBJ, then:

SELECT   DISTINCT OBJ, NUM,COUNT(NUM)
FROM     OBJECTS
WHERE NUM IN (2003,2004,2005)
GROUP BY OBJ, NUM
HAVING   COUNT(NUM) > 0
ORDER BY OBJ;

Will do that...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[getjbb]

This select is a subquery to another SELECT, so
if I want to the whole query to be

SELECT .....
FROM ....
WHERE OBJ IN (SELECT DISTINCT OBJ, NUM,COUNT(NUM)
FROM OBJECTS
WHERE NUM IN (2003,2004,2005)
GROUP BY OBJ, NUM
HAVING COUNT(NUM) > 0
ORDER BY OBJ;

it will not work.
I only need the OBJ values.

That is why the HAVING was present without an aggregate in the select. I was trying different ways to make it work.

Thanks.

 

[SantaMufasa]

getjbb,

Here is a solution you can assess:

select a.obj
from  (select obj, num num23 from objects where num = 23) a
     ,(select obj, num num24 from objects where num = 24) b
     ,(select obj, num num25 from objects where num = 25) c
where a.obj = b.obj
  and b.obj = c.obj
/

OB
--
AB
AD

Let us know if this resolves your need to your satisfaction.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

SELECT DISTINCT OBJ
FROM OBJECTS
where OBJ_ON_HAND > 0
AND NUM in (2003,2004,2005);


Bill
Oracle DBA/Developer
New York State, USA

 

[getjbb]

Thanks everyone for your help.

getjbb

 

[SantaMufasa]

Getjbb,

Since your original sample data did not include "obj_on_hand" values, I inadvertently omitted its consideration from my code. I have rectified that issue in my code re-post, below.

Also, I enhanced your sample test data to include the following situations:

1) Multiple rows with identical "OBJ" and "NUM" values)
2) A scenario with a single OBJ and just one of your target NUM values (i.e., "XX, 23, 1").
3) A scenario where an OBJ ("YY") has all three target NUM values (23, 24, and 25), but one of the rows has a zero OBJ_ON_HAND...Such a set should not be part of the result set.

Therefore, the sample data now appears as:

select * from objects;

OB        NUM OBJ_ON_HAND
-- ---------- -----------
AB         23           1
AB         24           1
AB         25           1
AC         26           1
AD         22           1
AD         23           1
AD         24           1
AD         25           1
AE         99           1
AE         20           1
AE         21           1
XX         23           1
AB         23           1
YY         23           0
YY         24           1
YY         25           1

16 rows selected.

Here is my revised code and its accompanying output:

select distinct a.obj
from  (select obj, num num23
         from objects
        where num = 23 and obj_on_hand > 0) a
     ,(select obj, num num24
         from objects
        where num = 24 and obj_on_hand > 0) b
     ,(select obj, num num25
         from objects
        where num = 25 and obj_on_hand > 0) c
where a.obj = b.obj
  and b.obj = c.obj
/

OB
--
AB
AD

And Bill,

Getjbb's original specs indicate that for an object to be part of the result set, an "OBJ" must have at least three rows and at least one of the rows must have a NUM=23, another row must have NUM=24, and a third row must have NUM=25.

Although your code coincidentally works with Getjbb's original sample data, your code (after replacing "2004, 2004, 2005" with "23, 24, 25" respectively) produces extraneous results with the more robust sample data (from above):

SELECT   DISTINCT OBJ
FROM     OBJECTS
where num > 0
and num in (23,24,25)
/

OB
--
AB
AD
XX
YY

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.