AW: Interesting question and debate on ddl tables with datefieldsthat will not always have a value

Hi,

Just FYI
NULL values and SQL queries
When using aggregate functions such as Sum(), Count() or Avg(), NULL values
are ignored.
This behavior may be uninteresting for the aggregate function Sum(), but may
return some unexpected results.

For example I have a table with 3 columns containing the following values:
Col1 Col2 Col3
1 1 1
1 1 NULL
1 NULL NULL
NULL NULL NULL

When executing following query;
select count(col1) as Cnt1, count(col2) as Cnt2,
count(col3) as Cnt3, Count(*) as CntAll
from MyTable

The result will be:
Cnt1 Cnt2 Cnt2 CntAll
3 2 1 4

Count(*) will count all rows even if there is a row with NULL values in all
Columns.
In this way, if all rows must be counted either use Count(*) or convert the
NULL values to a default value.
select count(coalesce(col1, 0)) as Cnt1, count(coalesce(col2, 0)) as Cnt2,
count(coalesce(col3, 0)) as Cnt3, Count(*) as CntAll
from MyTable

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Raul Alberto Jager
Weiler
Gesendet: Wednesday, 15. October 2008 23:51
An: Midrange Systems Technical Discussion
Betreff: Re: Interesting question and debate on ddl tables with
datefieldsthat will not always have a value

True for current RPG, not for SQL ¿improved RPG?

NULLs: select count(somefield) from table
Status: select count(somefield) from table where marker = off

And a big difference if you use Relational Integrity. Nulls don't need to
be in the parent table, markers requiere a special record, that needs to
be ommited in all queryes...