What does this SQL WHERE clause mean?

Question

Hi. I'm having some difficulty in understanding the following WHERE clause in a T-SQL (SQL Server 2000/2005) query:

update #tempTable
SET
    Total_Avg=isnull(TerminationReason,'terminated'),
    Individual_Subscriptions=null,
    Business_Subscriptions=null,
    Other_subscriptions=null,
    -- snip. 10 more fields set to NULL.
    PMIE_BI=null,
    Digital_Editions_BI=null
where
(
    AbcTerminationDate<=dbo.fnGetPeriodFinalDate(@periodid)
    and (AbcTerminationDate!=19000101 or  AbcTerminationDate is  null)
    and (Total_Avg is not NULL or PrevTotalAvg is not NULL)
)

Specifically, the second clause doesn't make sense to me - it's 2 sub-clauses separated by the OR operator seem contradictory.

The AbcTerminationDate field is declared as INT NULL in a table called Members. I believe a date of 19000101 in the system means NULL or a default value or no value, i.e. that a member is NOT terminated. So the query appears to blank out a whole lot of fields/figures if a member is marked as terminated, which would be when the AbcTerminationDate is NULL or has the default value.

Without knowing any more information, what do you make of it?

Answer 1

If the "or" arg were outside the () grouping it would negate:

AbcTerminationDate<=dbo.fnGetPeriodFinalDate(@periodid) 

[edit] Basically it's saying to take whatever results are true from that 1st clause, and perform an additional filter to make sure it's not 19000101 or it's null, are most likely exceptional values for the fnGetPeriodFinalDate function to properly evaluate.

Answer 2

It does look like those are contradictory. Perhaps they meant and !(AbcTerminationDate==19000101 or AbcTerminationDate is null) ?

Answer 3

Whatever 19000101 is "supposed" to mean, it is not the same as NULL in the eyes of the database. NULL is NULL. If you try to evaluate any other value to NULL then it can become problematic, because NULL means "unknown". For example, does 1=NULL? Maybe it does, maybe it doesn't. In fact, you can't even say that NULL=NULL, because each NULL is unknown so might or might not be equal to the other. It's safest to explicitly check for NULL conditions.

EDIT: As I point out in my comment, if NULLs are to be included then the first part of the query precludes that. Here is how it should be written if NULLs should be included:

(
    (
        (
            AbcTerminationDate <= dbo.fnGetPeriodFinalDate(@periodid) AND
            AbcTerminationDate != 19000101
        ) OR
        AbcTerminationDate is NULL
    ) AND
    (Total_Avg is not NULL or PrevTotalAvg is not NULL)
)