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

Raul Alberto Jager Weiler wrote:

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...

I'm trying to stay out of this conversation for the most part because it really is an opinion discussion. But we're sliding off into some overstatement.

For example, your count situation above only works for binary situations. That is, you are checking the status of a single field, and it can only have two states: count and not count. As soon as you need to check the status of multiple fields, the statement gets more complex. And NULLs don't work at all if you have fields that can have more than two states, each of which may need to be counted.

There's also the issue that the second line could be considered more self-documenting than the implicit loss of records of the first.

My point here is simple (and for those who know me, repetitive <smile>): neither technique is inherently better than the other, the correct design is the one that best fits the business requirement.

Joe