Aggregate functions on dBase tables with NULL numeric values 
Author Message
 Aggregate functions on dBase tables with NULL numeric values

I have to do summary operations on numeric fields in DBase tables.
Well, wouldn't you know that  dBase doesn't support DEFAULT values and
saves NULL into numeric fields if there is NO zero.

This gives SQL hell (I get Access Violations all over the place when I
execute:

SELECT
AKey,
SUM(ANumber) as NumTotal

From
MyTable

and there is a null value in [ANumber]

Adding a UNION statement to compensate for this works for only small
field aggregates but there are time when I have 4 or 5 aggregates I
need to perform on numeric fields and the combinations become
unmanagable!

So, my question is:

Is there a BDE setting I can use that will Treat dBase null numeric
values as 0?

If not, does anyone have ANY suggestions on how I can compensate for
this (other than changing database back ends...as that will come with
time but for now I have to live with this local table stuff...)

Bill Artemik
Programmer / Analyst
Droste Consultants, Inc.

"There are THREE kinds of people in the world...
      Those that can count and those that can't."



Wed, 18 Jun 1902 08:00:00 GMT  
 Aggregate functions on dBase tables with NULL numeric values

Quote:
>I have to do summary operations on numeric fields in DBase tables.
>Well, wouldn't you know that  dBase doesn't support DEFAULT values and
>saves NULL into numeric fields if there is NO zero.

It is normal database behavior for a column to contain a NULL value when
the column has never been assigned a non-NULL value or its non-NULL value
has been cleared. However, most database systems balance this with the
ability to specify a default value for a column -- something the dBASE
table specification does not make provision for. Compounding this
situation, local SQL does not fully accommodate the presence of NULL values
in numeric columns when aggregation occurs.

When using dBASE tables, to have a default value automatically assigned to
a column, you must do this programmatically in the application. One way is
to use a handler for the dataset component's OnNewRecord (or AfterInsert)
event. In the event handler, assign a value of your choice.

Quote:
>This gives SQL hell (I get Access Violations all over the place when I
>execute:

>SELECT
>AKey,
>SUM(ANumber) as NumTotal

>From
>MyTable

>and there is a null value in [ANumber]

What version of Delphi are you using? Version of the BDE? Table level for
that dBASE table? How was the .DBF file created: using the BDE or some
outside program?

While NULL values are not counted in aggregation, neither should their
presence induce an Access Violation. When does that error occur? When you
activate the TQuery? Or when you attempt to access the result set and its
values?

Your SQL statement as presented is not valid. You have both an aggregated
column (ANumber) and a non-aggregated column (AKey) in the SEKECT clause,
but no GROUP BY clause in the statement. This will not work in any version
of the BDE I have worked with. But the penalty for that is not an Access
Violation. It is merely an EDBEngineError exception with the message:

  "GROUP BY is required when both aggregate and non-aggregate fields are
  used in result set."

What was the *actual* SQL statement you were using?

Quote:
>Adding a UNION statement to compensate for this works for only small
>field aggregates but there are time when I have 4 or 5 aggregates I
>need to perform on numeric fields and the combinations become
>unmanagable!

>So, my question is:

>Is there a BDE setting I can use that will Treat dBase null numeric
>values as 0?

As stated previously, this is normal database behavior. Further, there is
no BDE setting for overriding this behavior. Your best bet is probably to
provide a means for assigning a default value (zero for a numeric column)
as described earlier.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Need help with type casting on numeric values from AVG aggregate functions

2. cannot read numeric value from a table

3. Problme to JOIN two table with SQL if one has NULL values

4. Adding numeric field to dBase-table ????

5. Adding numeric fields to dBase-tables

6. SQL - aggregate functions and groups

7. Newbie: SQL Aggregate Functions????

8. Aggregate functions and Group By problems in Local SQL

9. Creating new aggregate function in QuickReport

10. Aggregate functions in SQL

11. Aggregate functions on calculated fields

12. Trouble with the use of function findkey with dbase tables

 

 
Powered by phpBB® Forum Software