List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 27 1999 6:33pm
Subject:Re: Subtraction vapor
View as plain text  
Para-dox wrote:
> 
> Something seems to be causing inconsistancy...
> 
> SELECT (Sum(PAYMENTS.AMT)/100) FROM INVOICES LEFT JOIN PAYMENTS ON
> PAYMENTS.INVNUM = INVOICES.INVNUM WHERE INVOICES.INVNUM = 133674 GROUP BY
> INVOICES.INVNUM;
> 
> This returns one row with 134.56
> 
> SELECT (((((10000+INVOICES.TAXPERC+IF(INVOICES.HNDTYPE=0, INVOICES.HNDVAL,
> 0))*INVOICES.SUBTOTAL/10000)+IF(INVOICES.HNDTYPE<>0, INVOICES.HNDVAL,
> 0)))/100) FROM INVOICES LEFT JOIN PAYMENTS ON PAYMENTS.INVNUM =
> INVOICES.INVNUM WHERE INVOICES.INVNUM = 133674 GROUP BY INVOICES.INVNUM;
> 
> This returns one row with 134.56
> 
> SELECT (((((10000+INVOICES.TAXPERC+IF(INVOICES.HNDTYPE=0, INVOICES.HNDVAL,
> 0))*INVOICES.SUBTOTAL/10000)+IF(INVOICES.HNDTYPE<>0, INVOICES.HNDVAL,
> 0)))/100) - (Sum(PAYMENTS.AMT)/100) FROM INVOICES LEFT JOIN PAYMENTS ON
> PAYMENTS.INVNUM = INVOICES.INVNUM WHERE INVOICES.INVNUM = 133674 GROUP BY
> INVOICES.INVNUM;
> 
> This *should* return 0, I believe, but instead it returns -134.56  ??
> Am I forgetting something?
> 
> Regards, Dave (paradox@stripped)

Hi Dave

The syntax looks OK for me.
I also think it should return 0.
What version do you use?
I heard on this list about a GROUP BY problem in 3.23.
If you have this one, try to apply the patch from the mailinglist archive.

Tschau
Christian

Thread
Subtraction vaporPara-dox24 Jul
  • Re: Subtraction vaporChristian Mack27 Jul
  • Re: Subtraction vaporPara-dox27 Jul
  • Re: Subtraction vaporChristian Mack30 Jul
  • Subtraction vaporMichael Widenius2 Aug