Case condition in like?

[karthi2809]

Thanks in Advance .

I need to show status 

If the P_RETURN_STATUS is success then it SUCCESS,IF error then ERROR ,IF P_RETURN_STATUS is error and P_MESSAGE is NO NEW BATCH EXISTS as SUCCESS .But already the P_RETURN_STATUS having values as error .How to override when using AND condition

| eval P_RETURN_STATUS=case(like('P_RETURN_STATUS' ,"%SUCCESS%"),"SUCCESS", like('P_RETURN_STATUS',"%ERROR%"),"ERROR",like('P_MESSAGE',"%NO NEW BATCH EXISTS%") AND like('P_RETURN_STATUS',"%ERROR%"),"SUCCESS")

[richgalloway · Accepted solution]

The case function exits at the first match and does not evaluate the remaining expressions.  Put the AND condition first so it is evaluated before the others.

| eval P_RETURN_STATUS=case(like('P_MESSAGE',"%NO NEW BATCH EXISTS%") AND like('P_RETURN_STATUS',"%ERROR%"),"SUCCESS",
                            like('P_RETURN_STATUS' ,"%SUCCESS%"),"SUCCESS", 
                            like('P_RETURN_STATUS',"%ERROR%"),"ERROR",
                            1==1, "???")