Zeoslib Portal

Hi!

I'm have a GREAT trouble using the SUM command in my ZEOS. Hi is my system :

- Borland Delphi 7 and BDS 2006 (the trouble is the same in both)
- Zeos 6.5.1-alpha CVS-Version in Delphi 7
- Zeos 6.6.1 beta in BDS 2006
- MySQL 5.0.22
- Windows XP SP2

The trouble is basicaly I can't make a simple SUM in a QuickReport collumm (using a QREXPR component ).

I am not sure, but I think its because the ZQuery component makes the "SUM column" as TStringField in Zeos 6.5.1-alpha and TLargeintField in Zeos 6.6.1 beta.

The most strange is, if a make a SUM with a "non-SUM column" , the SUM works absoluty fine.

Example :

Table ORDERS in MySQL 5, innoDB :
ord_cod : integer(3)
ord_amount : integer(3)

Now,the following SQL statement using ZQuery:

"select ord_cod, sum(ord_amount) as ord_amount
from orders"

if I try make a SUM of "ord_amount", I will put a QREXPR component in the QuickReport and make the expression : Sum(ZQuery1.ord_amount) , right?

The Result : nothing. A blank field.

BUT, if I make a SUM with the field "ord_cod" the expression : Sum(ZQuery1.ord_cod) will works!

Anyone can understand what is wrong?

I think if ZEOS makes a SUM column as TIntegerField its might be works.

Thanks for any help,guys!

Hi,

Have you tried using a different alias (eg. sum_amount)? I suppose it will be the same result, but you never know zeos gets confused...
Let's talk about other possible reasons for the error.
How does QuickReport react to TLargeintField fields normally? Can you try using a mysql bigint field in your table? As far as I know Zeoslib just makes fieldtypes as Mysql reports it's returning. The sum of a lot of integers may be very big, so a bigint result seems logical. And we have to use a TLargeintField to return that. You could try 'Cast(sum(ord_amount) as integer)' in your query, but I'm not sure he doesn't cast as a bigint.

Mark

Hi!

Finally I solved the problem.

Mdaems, I tried use a diferent alias and the problem stills.
Making the test you ask, I found that QuickReport does not SUM any type of TLargeintField, not only the "SUM fields".

My solution :

select ord_cod, cast(sum(ord_amount) as UNSIGNED) sum_amount

With this CAST, the "sum_amount" will be a TFloatField , and the SUM in Quickreport works perfect now.

I don't know why, but if I try :

"cast(sum(ord_amount) as integer) sum_amount" ,

the MySQL5 not accept it as a legal select.

Thanks for the help!

IF YOU ARE USING MYSQL 5, PLEASE CONSIDER THAT FLOAT TYPE OF
MYSQL IS BUGGY. TRY TO CHANGE TO DOUBLE TYPE.
REGARDS