Tim

>The simple answer is that parameters are an exception because they are a
>runtime function, and the SQL compiler cannot possibly check their values.
>Think of parameters like column values, and you'll understand. For example,
>this is completely valid:
>
> select PatientenID,privat from PraxisScheine where PatientenID = 100
>
>yet PatientenID could very well be NULL in some rows, resulting in an actual
>comparison of:
>
> select PatientenID,privat from PraxisScheine where NULL = 100
>
>of which the result is False.

Shouldn't your example have a result of NULL? At least that's the conclusion that testing in EDBManager leads me to.

Does this also mean that UDFs returning a NULL can be tested with = and <> rather than IS NULL and IS NOT NULL?

I suspect I'm never going to understand NULL.

Roy Lambert

Roy,

<< Shouldn't your example have a result of NULL? At least that's the
conclusion that testing in EDBManager leads me
to. >>

I was referring to the result of the WHERE clause condition, not the result
set.

<< Does this also mean that UDFs returning a NULL can be tested with = and
<> rather than IS NULL and IS NOT NULL? >>

Only in the sense that I've already described for columns and parameters.
Imagine a scenario where we issued an exception every time a
column/function/parameter returned a NULL value for an equivalency test.  It
would make EDB virtually unusable because SQL statements that worked fine in
testing would fail simply because the values in the source tables changed
and now include NULLs.

<< I suspect I'm never going to understand NULL. >>

Well, I suspect that you're making it harder on yourself than you need to by
constantly questioning the way NULLs are handled.  Just remember the rules
and you'll be fine.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< I suspect I'm never going to understand NULL. >>
>
>Well, I suspect that you're making it harder on yourself than you need to by
>constantly questioning the way NULLs are handled. Just remember the rules
>and you'll be fine.

Its not the rules its the exceptions - nearly as bad as English spelling.

Roy Lambert