Hi,

<<
You can now specify whether you want ANSI-SQL standard NULL behavior
(the default), or non-ANSI-SQL NULL behavior where NULLs are comparable
with normal operators (=,<>,>,<,BETWEEN, etc.) and NULL values are
treated like an empty version of the containing value's data type.
>>

Does that mean that I can use:

select * from MyTable where MyValue = NULL

instead of

select * from MyTable where coalesce(MyValue,'') = ''

IOW: is "=Null" now the same as "=''"?

Uli

Uli,

<< Does that mean that I can use:

select * from MyTable where MyValue = NULL

instead of

select * from MyTable where coalesce(MyValue,'') = ''

IOW: is "=Null" now the same as "=''"? >>

Not exactly.  This change does not make NULL=<EmptyString>, it simply makes
NULL usable with operators that normally it isn't usable with due to the
standard.  You still have to have NULL=NULL in order for the result to be
True.

For example, with the ANSI-standard behavior:

SELECT * FROM MyTable WHERE MyStringColumn=NULL

causes an error.  With the non-standard behavior, this statement will return
all rows where MyStringColumn *is actually NULL*, but not any rows where
MyStringColumn is an empty string.  IOW, the non-standard behavior does not
make ElevateDB start working like DBISAM did with respect to string columns.
It does, however, make it behave like DBISAM in terms of allowing NULLs to
be used directly with comparison operators other than IS/IS NOT NULL.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

I read this "and NULL values are treated like an empty version of the containing value's data type" and thought woopee back to a good approach.


I was waiting until I could do some testing because I was sure Santa wasn't due just yet

Roy Lambert

Roy,

> I read this "and NULL values are treated like an empty version of the containing value's data type" and thought woopee back to a good approach.

I thought exactly the same. Thinking of you as well.

Uli

Roy,

<< I read this "and NULL values are treated like an empty version of the
containing value's data type" and thought woopee back to a good approach. >>

"Empty" in that statement means "not present", or NULL.  I should have used
the words "blank string" in my previous message to avoid confusion.

Just remember that this feature does not change the core behavior of a NULL,
only how NULLs can be used with comparison operators.

--
Tim Young
Elevate Software
www.elevatesoft.com