GROUP BY Cursors

[Ruslan Zasukhin]

on 3/18/03 3:54 AM, Gunnar Swan at GunnarSwan at practicetopass.com wrote:

> Hello,
> 
> During development, we have been using a number of different data types.
> Boolean, String, Text, Long, ULong, UShort and FLOAT.
> 
> The application itself is done, and has been. Now, we are working more and
> more on reporting. Reporting involves GROUP BY .
> 
> We are noticing (without testing every data type) that if we use SUM(field)
> COUNT(field) in a GROUP BY, that if the field is not declared as a FLOAT, we
> get a NULL or VOID result in the cursor.

> As a result, we are changing our database schema so that all fields that are
> SUM'med or COUNT'ed in a GROUP BY ... are now stored in Valentina as FLOAT.

> Then, we are scouring our programs to convert to FLOAT before a store in the
> database.
> 
> Once we have made this change, there is no reason to go back, as this would
> mean that moving from FLOAT to ULONG on deployed software would require update
> routines.
> We do not feel that our converting to FLOAT represents any problem, other than
> some minor slow-down as a result of type-casting within the code.
> 
> But ... for new products we are soon to roll out, we would like to use other
> data types (other than FLOAT) for data, where the fields will be involved in
> GROUP BY Summation and other
> aggregate functions.
> 
> Is it possible for more testing to be done with aggregate functions and GROUP
> BY ... on data-types other than FLOAT ?
> Is this a problem with what we are doing, or is there an actual issue with
> aggregate functions and GROUP BY cursors ?

Wait the moment. Let's clarify.

1) Yes you must be able use SUM() for Integer fields.
    
2) Let you have in table fields f1, f2, f3.
    Note that even if this fields are Integers, then __result__ of AVG()
will be FLOAT. So column AVG() in SELECT is FLOAT column.

3) COUNT(field) column is INTEGER column,
    and field can be of ANY type, even string.

4) You say that COUNT( fldLong ) returns VOID ???

Hmm, I need to check this.

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://listserv.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------