Re: Max date

Just to throw something into the fray that is really a non-winning attempt, but I'll do it anyway!

NULL - it is not really a value, it is an attribute. That's why the null indicators make some sense - it's either true or false that the value of something is known. Not that this makes things easy.

When columns are nullable, the row in storage has a bit array (I think - maybe a byte array) up to 8000 long - the maximum number of columns in a record.

There is no test in SQL for FIELD = NULL - the test is FIELD IS NULL.

The COALESCE function is always described as "...returning the 1st non-null expression among it's arguments..." I suggest that "non" is a test for "is", not "equals".

I really do think that if we remember this distinction, that things can be a little less confusing. As Charles says, you can't say a comparison is true or false when a column could BE NULL in some rows.

The trouble with this position is, even IBM in its documentation talks about a column having the NULL value - I think! Even people in this list find this to be common usage.

Anyhow, there I sit - ready to to be knocked off my NULL position on the fence into either the TRUE or FALSE side - hope it's greener, wherever I land!

Vern

On 9/23/2015 3:58 PM, Charles Wilt wrote:

On Wed, Sep 23, 2015 at 2:13 PM, John Yeung <gallium.arsenide@xxxxxxxxx>
wrote:

Let's say I have a nullable variable call MYVAR. And let's say that
its null indicator is currently on. So in SQL, MYVAR is simply equal
to NULL. But in RPG, I can do this:

myvar = 5; // null indicator is not affected
x = %nullind(myvar); // x = '1'
y = (myvar > 2); // y = '1'

In SQL, MYVAR > 2 would be False, since any comparison with NULL is
False.

​No, no it wouldn't and no, no it isn't...

In SQL,
when MYVAR is NULL
​ then ​
MYVAR > 2 is unknown; neither true or false.

​If as you say MYVAR > 2 was FALSE, then NOT (MYVAR > 2) would be true and
it's not.

I'm sure you realize this, but it just goes to show how easy it is to screw
up 3 valued logic.

Going back to the OP, if expiration date is nullable with NULL standing in
for NO EXPIRATION, then​ the only way to return a list of non-expired
records would be to
select *
from myfile
where myexp is null or myexp > CURRENT_DATE

You can pull a list of expired records with a simple
select *
from myfile
where myexp <= CURRENT_DATE

And boolean logic would tell you that this should give non-expired
records...
select *
from myfile
where NOT (myexp <= CURRENT_DATE)

But it won't when myexp contains a NULL value.