Stack Exchange
Case Statement MySql
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?

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.:-)

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

'Pending' its not showing. Here in this case 'ISAC' is Pending. But as per the case statement above its showing 'Completed'. Am i making any mistake?

@SanjuMenon Maybe open_auth_date is not null in your case?

The parentheses are not needed.

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`


The comparison operator (=) has higher precedence than OR. This means tender_cust_shortcode = 'HAL Hydera' OR 'HAL ARDC' is evaluated as (tender_cust_shortcode = 'HAL Hydera') OR 'HAL ARDC' and this is not what the OP needs.

@Avani. Thank you for your time.
