CODEWITHSUNDEEP
phpmysqlif-statementcase
Sanju Menon
Sanju Menon

Reputation: 729

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

Upvotes: 1

Views: 166

Answers (1)

rbr94
rbr94

Reputation: 2287

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

Upvotes: 1

Related Questions