At 01:42 PM 17/11/2004 +0100, FSG wrote:

>Gert Coetzee wrote:
>
> > How would I do this in Firebird ?
>
>You may try sth. like this:
>
> > SELECT MatchingRef AS Ref,
> > Sum(
>
> case
> when (ORIGINAL = '1') then Amount
> else 0
> end
> )
> AS InvAmt,
>
> > Sum(Amount) AS Outstanding,
> > Min(
>
> case
> when (Original ='1') then (cast IDate as
>char(10))
> else 'xxxxxxx'
> end
> )
> AS Date1
>
> > FROM TempCashup

II will be necessary also to remove the invalid grouping criteria, or else
include them in the SELECT list; and you should also move the WHERE
criteria into their correct place, because it adds a lot of overhead to
delay the filtering until after the intermediate set is formed:

WHERE DC = 'D'
AND
Account= 'PIT'

GROUP BY MatchingRef, /* DC, Account */
HAVING Sum(Amount) >= 0.01
ORDER BY MatchingRef;

./hb