SQL case expression with where condition-CodePudding

how can I arrange the case expression of sql in the correct format as I am getting errors. I am trying to make a condition if the field date of leaving is not null display left otherwise display active

SELECT DISTINCT  
PEO.PEOPLE_ID AS [PEOPLE_ID]  
,PEO.EMPLOYEECLOCKNO AS [EMPLOYEENUMBER]  
,ISNULL(CONVERT(VARCHAR(50),PEO.DATEOFJOIN,103),'') AS [DATEOFJOIN]  
--,ISNULL(PEO.DISPLAYSTATUS,'') AS [DISPLAYSTATUS]
,CASE 
    When ISNULL(PEO.STATUS,'' ) = 'A' 
    Then 'ACTIVE'
    ELSE 'LEFT' WHERE PEO.DATEOFLEAVING <> NULL
END as [STATUS]
  
INTO #BSP_EMPLOYEEDETAILS

Thank you for your help.

CodePudding user response：

I am trying to make a condition if the field date of leaving is not null display left otherwise display active

CASE WHEN PEO.DATEOFLEAVING IS NULL THEN 'ACTIVE' ELSE 'LEFT' END

(I prefer to make boolean checking positive-feeling rather than negative-feeling, but if you want it negative...)

CASE WHEN PEO.DATEOFLEAVING IS NOT NULL THEN 'LEFT' ELSE 'ACTIVE' END

Note well HoneyBadger's comment; you should only compare something to null using IS NULL or IS NOT NULL - the result of a null right operand with any non-IS operator, is null:

NULL IS NULL     --true
NULL IS NOT NULL --false
NULL = NULL      --null, which eventually will (probably) be processed as false
NULL <> NULL     --null, which eventually will (probably) be processed as false
NULL != NULL     --null, which eventually will (probably) be processed as false
NOT(NULL = NULL) --null, which eventually will (probably) be processed as false

I say "eventually" because it's important; null spreads. If you look at the last case, NULL=NULL is NULL, which then becomes NOT(NULL) which is also NULL. If NULL=NULL instantly became FALSE then NOT(NULL=NULL) would be NOT(FALSE) which would be TRUE. The fact that the null spreads throughout the entire set of operations you subject it do, popping out at the end and then being interpreted as false is something that may be advantageous, but always something you should be aware of...

Of course if the thing that receives it is intended for processing NULL, like COALESCE(NOT(NULL=NULL), 'it was null') then it will receive a null (and in this case would output 'it was null') but generally when youre using it in a boolean context like CASE WHEN NOT(mycolumn=NULL) THEN... or WHERE NOT(mycolumn=NULL) the NULL it becomes is received by case when/where and interpreted as false.

It's probably worth pointing out that SQLServer doesn't support booleans in these sort of contexts so you wouldn't get away with using a coalesce like that, or even the NOT(NULL=NULL) example but if you want to experiment to sound out your knowledge, MySQL is a lot more forgiving, and will allow queries like:

select case when (NOT(x = NULL)) IS NULL THEN 't' ELSE 'f' end
from (select null as x)y

select case when (NOT(x = NULL)) IS UNKNOWN THEN 't' ELSE 'f' end
from (select null as x)y

SQLServer expects you to make your null determinations sooner, and doesn't let you bandy around with things that it can see have become null/unknown

_{Footnote about UNKNOWN; it's generally treated (in the spec, even) as synonmous with a boolean NULL - it's talked about in the truth table sense as the third value in a tri-state boolean logic, but DBs generally make for interchangeable use if they support UNKNOWN as a keyword. For me, personally, I like one-less-thing-to-remember; you can learn how NULL behaves and achieve logically consistent results by applying those simple rules: IS NULL, IS NOT NULL, anything else becomes NULL which then nulls everything it touches}