Re: Oracle query

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 11 Aug 2006 16:57:47 -0700
Message-ID: <1155340668.170593@bubbleator.drizzle.com>

Amritha.Datta_at_gmail.com wrote:

> Can you please give me an example of doing it?
> I appreciate your help.
> 
> Thanks.
> 
> DA Morgan wrote:
>> Amritha.Datta_at_gmail.com wrote:

>>> Can anyone help me to get results from this tricky query:
>>>
>>> Select Recnum, trim(Both From Family_ID), trim(Both From DOB)
>>> From abc
>>> Where File_Key = 2470 And Sub_File_Key = 1 and Reason is null and
>>> trim(Both From Family_ID) IN
>>> (
>>> Select trim(Both From Family_ID) From abc Where
>>> File_Key = 2470 And Sub_File_Key = 1 and Reason is null
>>> And trim(Both From Family_ID) IN
>>> (
>>> select trim(Both From Family_ID) From abc Where
>>> File_Key = 2470 And Sub_File_Key = 1 and reason is null
>>> group by trim(Both From Family_ID) having count(trim(Both From
>>> Family_ID)) > 1
>>> )
>>> group by trim(Both From Family_ID), trim(Both From DOB) having
>>> count(trim(Both From DOB)) > 1
>>> )
>>> group by trim(Both From Family_ID), trim(Both From DOB) having
>>> count(trim(Both From Family_ID) || trim(Both From DOB)) > 1
>>>
>>> I know I should not use group by with out all the fields listed in
>>> select statement. But my intension is to have Recnum based on these
>>> conditions.
>>>
>>> Can anyone come across this kinda issue? Is there any work arrond on
>>> this ?
>>>
>>>
>>> Thanks.

>> Do your GROUP BY as an in-line view and then join it in another query
>> where you grab recnum.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
> 

1. Do not top post
2. It isn't pretty or useful but it will give you the syntax:

desc all_objects;

SELECT tablespace_name, COUNT(*)
FROM all_tables
GROUP BY tablespace_name;

SELECT tablespace_name, COUNT(*)
FROM all_tables
GROUP BY tablespace_name
HAVING COUNT(*) > 100; SELECT ilv.tablespace_name, at.table_name FROM (
   SELECT tablespace_name, COUNT(*)
   FROM all_tables
   GROUP BY tablespace_name
   HAVING COUNT(*) > 100) ilv,
   all_tables at
WHERE ilv.tablespace_name = at.tablespace_name;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Aug 11 2006 - 18:57:47 CDT