Amritha.Datta_at_gmail.com wrote in
news:1155317608.405768.130700_at_i3g2000cwc.googlegroups.com:
> Thanks Maxim. This works!
> Would like to read more about PARTITION BY Syntax. Could you provide
> some links to see the details.
>
> Thanks.
>
> Maxim Demenko wrote:
>> Amritha.Datta_at_gmail.com schrieb:
>> > 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.
>> >
>>
>> At first look i have the impression (which may of course be wrong),
>> that should do what you ask.
>> SELECT Recnum
>> FROM (SELECT Recnum,
>> COUNT(*) Over(PARTITION BY TRIM(Family_Id),
>> TRIM(Dob)) Cnt
>> FROM Abc
>> WHERE File_Key = 2470
>> AND Sub_File_Key = 1
>> AND Reason IS NULL)
>> WHERE Cnt > 1
>>
>>
>> Best regards
>>
>> Maxim
>
>
- do NOT top post
- http://tahiti.oracle.com
Received on Sat Aug 12 2006 - 08:17:15 CDT
