Re: SQL query help

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Fri, 11 Aug 2006 17:31:58 +0200
Message-ID: <44dca491$0$20032$9b4e6d93@newsspool4.arcor-online.net>

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 Received on Fri Aug 11 2006 - 10:31:58 CDT