[Gambas-user] Bug when executing SQL..

[Stephen Bungay]

COALESCE, like NULLIF, deals with a list of values that may or may not be 
null. In the example, if SUM(rec_payments) returns a NULL then the COALESCE 
will RETURN a zero (0).

  Aside from that the SELECT is pretty straight forward. If the COALESCE is 
removed and the statement changed to read as follows;

SELECT SUM(rec_payment) 
FROM tbl_salespayments
WHERE rec_create > 1158921569; 

  It still returns the sum, when in fact it should return a null field.

Steve.

On Tuesday 22 August 2006 14:08, Benoit Minisini wrote:
> On Tuesday 22 August 2006 19:50, Stephen Bungay wrote:
> > The reason I say this is because I am getting some rather unexpected
> > results. For example the select statement;
> >
> > SELECT COALESCE(SUM(rec_payment), SUM(rec_Payment),0) AS Payment
> > FROM tbl_salespayments
> > WHERE rec_create > 1158921569;
> >
> >   When executed from within the database manager  (or my gambas
> > application) returns the sum of the column rec_payment, but when executed
> > in MySQL it returnes zero (0) (which is what it should return in this
> > case given the data I have in the table).
> >  Are there known issues with whatever engine is being used?
> >
> > Steve.
>
> There are no specific hack when using Exec() in Gambas, except the
> substitution of '&X' patterns, but you don't have any of one in your
> request.
>
> You should give more detailed informations (the more as you can). And tell
> me what does COALESCE, because it is the first time I see it. :-)
>
> Regards,