Re: Adding the pennies correctly

From: Ian Brown <browni_at_globalnet.co.uk>
Date: 30 Jun 1999 17:55:56 GMT
Message-ID: <01bec322$18a4a3e0$LocalHost_at_default>

Many thanks for your input - I realise now I should have given more information at the outset.

Answers were obtained using nothing other than SQL*Plus.

The first query was as follows:

select

T.YEAR,
T.R_CAT,
T.DOC_TYPE,

NVL(T.SUSPENSE,'N') SUSP,
DECODE(SIGN(T.IN_DATE-TO_DATE('01-APR-98')),-1,'N','Y') IN_YEAR, COUNT(T.IN_DATE) NO_RECS,
SUM(nvl(T.AMOUNT,0)) SUM_OF_AMOUNT
from TOY_FL_TRANSACT T
group by

T.YEAR,
T.R_CAT,
T.DOC_TYPE,

NVL(T.SUSPENSE,'N'),
DECODE(SIGN(T.IN_DATE-TO_DATE('01-APR-98')),-1,'N','Y')
/

The second as follows:

select
/*+ use_hash(P) */

T.YEAR,
T.R_CAT,
T.DOC_TYPE,

NVL(T.SUSPENSE,'N') SUSP,
DECODE(SIGN(T.IN_DATE-TO_DATE('01-APR-98')),-1,'N','Y') IN_YEAR, RPAD(P.PRP_REF,2) DISTRICT,
COUNT(T.IN_DATE) NO_RECS,
SUM(NVL(T.AMOUNT,0)) SUM_OF_AMOUNT
from PROPERTY P,
TOY_FL_TRANSACT T
where P.PRP_NUM(+)=T.PRP_NUM
group by

T.YEAR,
T.R_CAT,
T.DOC_TYPE,

NVL(T.SUSPENSE,'N'),
DECODE(SIGN(T.IN_DATE-TO_DATE('01-APR-98')),-1,'N','Y'), RPAD(P.PRP_REF,2)
/

There is an overall difference in the grand total of somewhere in the region of £4.89 but I have bee able to pinpoint a difference of 3p arising in one district on one DOC_TYPE (all the other districts show 0.00 for that DOC_TYPE). The total in the original script was 0.00 for the same DOC_TYPE.

That's all the gory detail.

Thanks for any light you may be able to shed. Received on Wed Jun 30 1999 - 19:55:56 CEST