Board index » delphi » Aggregate functions on dBase tables with NULL numeric values

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.
b...@droste1.com

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

 

Re:Aggregate functions on dBase tables with NULL numeric values


On 7 Oct 1999 08:52:02 -0500, b...@droste1.com (Bill Artemik) wrote:

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

Other Threads