Case Statement MySql

Question

Iam trying to write a CASE statement for the below Table structure:

========================================
tender_cust_shortcode | open_auth_date
========================================
HAL Hydera            |   24-01-2016
DRDL                  |   NULL
HAL ARDC              |   10-02-2016
DLRL                  |   NULL
ISAC                  |   NULL
========================================

My CASE statement is as follows:

CASE WHEN (tender_cust_shortcode = 'HAL Hydera' OR 'HAL ARDC') THEN 'N/A'
     WHEN (tender_cust_shortcode != 'HAL Hydera' OR 'HAL ARDC') AND open_auth_date IS NULL 
THEN 'Pending'  
ELSE 'Completed' END AS `Open Auth Status`

The criteria is

1) if (tender_cust_shortcode = 'HAL Hydera' OR 'HAL ARDC') Then it should result as 'N/A'

2) if (tender_cust_shortcode != 'HAL Hydera' OR 'HAL ARDC') AND open_auth_date IS NULL then it should result as 'Pending'

3) Else 'Completed'.

Iam not getting the desired output. May be AND or OR condition problem. Please can anyone help me on this?

Answer 1

Try to use IN, not sure if this works, but try it:

CASE
    WHEN tender_cust_shortcode IN ('HAL Hydera', 'HAL ARDC') THEN 'N/A'
    WHEN tender_cust_shortcode NOT IN ('HAL Hydera', 'HAL ARDC') AND open_auth_date IS NULL THEN 'Pending'  
    ELSE 'Completed'
END AS `Open Auth Status`

Here is SQLFiddle Demo, it works.:-)

Answer 2

You have to repead the column name and the or unequals condition must connected by AND:

CASE WHEN (tender_cust_shortcode = 'HAL Hydera' OR tender_cust_shortcode = 'HAL ARDC') THEN 'N/A'
     WHEN (tender_cust_shortcode != 'HAL Hydera' AND tender_cust_shortcode != 'HAL ARDC') AND open_auth_date IS NULL 
THEN 'Pending'  
ELSE 'Completed' END AS `Open Auth Status`

See SQLFiddle

Answer 3

Try a query like this:

SELECT CASE WHEN (tender_cust_shortcode = 'HAL Hydera' OR 'HAL ARDC') THEN 'N/A'
WHEN ((tender_cust_shortcode != 'HAL Hydera' OR 'HAL ARDC') AND (open_auth_date IS NULL)) THEN 'Pending'
ELSE 'Completed' END FROM `test`