numeric sign reverses in query

[fumbles]

Hi all -

This is probably something very simple but I am stumped. I have a query that I run over a sales detail table. Two of the fields are numeric 8,2 ( amount and cost ). When the query encounters a reversing entry where 499.00 becomes -499.00 and 251.25 becomes -251.25 ( the reversing record is an exact copy except for the minus in 3 fields and a cancellation flag ), the result set contains either two positives or two negatives.

It is as if the query is ignoring the sign in the original.

Some condensed code:

SELECT Deliverd.dl_sku,;
Deliverd.dl_ven,;
Deliverd.dl_item,;
Deliverd.dl_ctrlno,;
Deliverd.dl_cost,;
Deliverd.dl_bkcost,;
Deliverd.dl_amnt,;
Deliverd.dl_inv, ;
Deliverd.dl_custnum,;
Deliverd.dl_invdate,
Dldhdr.sh_cusnam,;
Dldhdr.sh_slsmn1,;
Dldhdr.sh_store ;
FROM itwinv!deliverd INNER JOIN itwinv!dldhdr ;
ON Deliverd.dl_inv = Dldhdr.sh_invoice;
INTO TABLE slshist.dbf

dl_cost,dl_bkcost & dl_amnt all exhibit the symptoms - all are N 8,2



Help


Steve

 

[baltman]

You want to do a GROUP BY Deliverd.dl_sku and execute a SUM() aggregation in the fields where you need them so that the amount and the reversal cancel out.

Brian

 

[ramani]

Hi Steve,

It appears that your SQL syntax is correct. So no reason for getting double - or double +ve.

What I feel is that, you are storing the figures as +ve or -ve and only read it in conjuction with the cancel flag. In other words, the data is saved as of the same sign only and so, you are getting the double effect.

SO.. probably, you would want to reconstruct the SQL syntax.. as..

SELECT Deliverd.dl_sku,;
Deliverd.dl_ven,;
Deliverd.dl_item,;
Deliverd.dl_ctrlno,;
IIF(cancelFlag, 0-Deliverd.dl_cost, Deliverd.dl_cost) ;
AS dl_cost ;
IIF(cancelFlag, 0-dliverd.dl_bkcost, dliverd.dl_bkcost) ;
AS dliverd.dl_bkcost ;
IIF(cancelFlag, 0-Deliverd.dl_amnt, Deliverd.dl_amnt) ;
AS Deliverd.dl_amnt, ;
Deliverd.dl_inv, ;
Deliverd.dl_custnum,;
Deliverd.dl_invdate,
Dldhdr.sh_cusnam,;
Dldhdr.sh_slsmn1,;
Dldhdr.sh_store ;
FROM itwinv!deliverd INNER JOIN itwinv!dldhdr ;
ON Deliverd.dl_inv = Dldhdr.sh_invoice;
INTO TABLE slshist.dbf

Suitably change CancelFlag field name in above



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[fumbles]

Ramani & Baltman...
As always thank you for your prompt help. I tested both of your ideas and in the process, while changing my approach to use Ramani's suggestion, I discovered a duplicate record in a related table. Eliminating the duplicate removed the symptom. Thanks again.

Steve