OraFAQ Forum: SQL & PL/SQL » Search Oracle table and only list data depending on rows

Home » SQL & PL/SQL » SQL & PL/SQL » Search Oracle table and only list data depending on rows (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Search Oracle table and only list data depending on rows [message #642439]

Thu, 10 September 2015 17:39

aft5425@anthem.com
Messages: 11
Registered: September 2015

Junior Member

I have a table defined as follows:

GROUP
group_nbr varchar2(10)
agent_nbr varchar2(10)
release_dt date

...with following data

1111 rwo 9-1-2015
1111 tmk 9-5-2015
1111 pwc

Row 3 has no release (null). Group nbr is the same for all 3 records. There can be 0 to many records for this group in the group table.

I only want the record if there if the release_dt is not null for any of the record for group 1111.

Here is my sql below. Why wouldn't this work? I get the errors below.

Report message to a moderator

Re: Search Oracle table and only list data depending on rows [message #642441 is a reply to message #642439]

Thu, 10 September 2015 18:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>Here is my sql below. Why wouldn't this work? I get the errors below.
Please try again.

Report message to a moderator

Re: Search Oracle table and only list data depending on rows [message #642442 is a reply to message #642439]

Thu, 10 September 2015 22:32

aft5425@anthem.com
Messages: 11
Registered: September 2015

Junior Member

I can see that my SQL didn't paste properly so I'm updating:

I have a table defined as follows:

Table: GROUP
group_nbr varchar2(10)
agent_nbr varchar2(10)
release_dt date

...with following data

1111 rwo 9-1-2015
1111 tmk 9-5-2015
1111 pwc
2222 tko 8-8-2015
2222 ppt 8-1-2015
2222 nnw 7-1-2015
2222 nlw 7-2-2015
2222 nnp 7-9-2015
2222 owa
3333 bil 6-1-2015
3333 att 6-2-2015

There can be 0 to many records for this group in the group table, but I only want select the groups that have a release date for all the records. For example, for group 1111 the 3rd record has a null release_dt, so I don't want that group included in my result. However group 3333 has no records without a release date so I do want that group.

Here is my sql below. Why wouldn't this work? I get the errors below.

select group_nbr, count(*) from GROUP
where release_dt is null
group by group_nbr
having count(*) < 1

I want group_nbr 3333 to appear in my result only, but I'm not getting anything in my result set. I previously said there was a SQL error, but that was my mistake. Technically, all I want returned in the result set is the group_nbr.

Report message to a moderator

Re: Search Oracle table and only list data depending on rows [message #642443 is a reply to message #642442]

Thu, 10 September 2015 22:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I want group_nbr 3333 to appear in my result only
Why?
GROUP_NBR=3333 has no rows WHERE RELEASE_DT IS NULL

It appears you either did not read my previous post, or your read it & then totally ignored what the Posting Guidelines state.
Which is it?

Report message to a moderator

Re: Search Oracle table and only list data depending on rows [message #642444 is a reply to message #642443]

Fri, 11 September 2015 00:14

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

When you fake table name, don't do it in an obviously wrong manner:

SQL> create table group (col number);
create table group (col number)
             *
ERROR at line 1:
ORA-00903: invalid table name

Here are two options; someone smarter than me might provide better one(s).

select group_nbr
from your_table
group by group_nbr
having sum(decode(release_dt, null, 1, 0)) = 0

select group_nbr from your_table where release_dt is not null
minus
select group_nbr from your_table where release_dt is null;

Report message to a moderator

Previous Topic:	Compare 2 encrypted values
Next Topic:	Consolidated output from a LOOP

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 20:03:00 CDT 2024