Issue in Case Statement

Question

I have an issue in this CASE Statement. Hopefully its a syntax issue.

CASE quot_status.open_auth_date IS NULL
WHEN 1 THEN 'Pending' 
WHEN 2 THEN 'Completed' 
ELSE IF WHEN tender.open_authorization > quot_status.open_auth_date THEN 'Due' 
END 
AS `Open Auth Status`

In the above tender.open_authorization and quot_status.open_auth_date are like comparing the dates.

Let me explain the scenario. i have two date fields in my mysql db. First field is called 'quot_status.open_auth_date' and second field is 'tender.open_authorization'. I need to check for 3 conditions here. First condition is i need to check if 'tender.open_authorization > quot_status.open_auth_date' Then Open Auth Status should show the result 'Due'. If its due then Case Statement has to be Exited or END. Second condition is i should check if quot_status.open_auth_date = 'NULL' , if its NULL Open Auth Status should show the result as 'Pending' else its hould show 'Completed'.

Answer 1

CASE quot_status.open_auth_date 
       WHEN 1 THEN 'Pending' 
       WHEN 2 THEN 'Completed'
       WHEN  tender.open_authorization >  quot_status.open_auth_date THEN 'Due' 
END AS `Open Auth Status`

Try This code

Answer 2

your format has some issues, You may follow the below syntax and make your query....

    CASE  
            WHEN (ISNULL(quot_status.open_auth_date) && < status=1 > )     THEN 'Pending' 
            WHEN (ISNULL(quot_status.open_auth_date) && < status=2 > )     THEN 'Completed' 
            WHEN (tender.open_authorization > quot_status.open_auth_date ) THEN 'Due' 
                                                                           ELSE '< Unknown >'
    END AS `Open Auth Status`