Oracle query

From: <Amritha.Datta_at_gmail.com>
Date: 11 Aug 2006 07:54:58 -0700
Message-ID: <1155308098.107181.128820@74g2000cwt.googlegroups.com>

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. Received on Fri Aug 11 2006 - 09:54:58 CDT