Sanju Menon Sanju Menon - 2 months ago 9
MySQL Question

CASE Statement to check if any field is NULL

There are two fields in my table cust_shipmentdate_awb and comp_shipdate_awb. Iam trying to write a CASE Statement which should check both fields cust_shipmentdate_awb and comp_shipdate_awb is null or empty. If both are Null then show the result as 'Pending'. If any one field is not empty, then it should show as 'Completed' AND if both fields are entered also it should as 'Completed'. I have tried the below CASE statement. But its showing pending when both the fields data is entered. Iam trying to achieve as per the below data.

============================================================
cust_shipmentdate_awb | comp_shipdate_awb | shipment_status
============================================================

02-03-2016 | | Completed
| 09-08-2016 | Completed
NULL | NULL | Pending
01-06-2016 | 09-08-2016 | Completed
============================================================

CASE
When cust_shipmentdate_awb Is Null Or comp_shipdate_awb = '' Then 'Pending'
Else 'Completed' End AS shipment_status

Answer

Just try to use the AND operator and check both fields for null and empty (''):

CASE    
    When        (cust_shipmentdate_awb Is Null OR cust_shipmentdate_awb = '') 
            AND (comp_shipdate_awb Is Null OR comp_shipdate_awb = '') Then 'Pending' 
    Else 'Completed' End AS shipment_status
Comments