CODEWITHSUNDEEP
sqlsyntax
Mark
Mark

Reputation: 215

SQL Case statement replacing values incorrectly

I'm trying to rename the values of one column of a database I have READ-ONLY access to. I also want to combine two of the values into one. I'm using a SQL CASE query, but it is converting values incorrectly. Here is my SQL:

SELECT
DATE(appraisal.created_time),
appraisal_steps.title,
Count(appraisal.appraisal_id),
case appraisal_steps.title
 when "archive" then "A_Archived"
 when "on_sale" then "J_Selling"
 when "under_evaluation" then "F_Evaluating"
 when "evaluated" then "H_Appraisal Complete"
 when "pending" then "B_Pending"
 when "crap" then "C_Not For Lofty"
 when "waiting_internal_expert_evaluation" then "D_Unclaimed"
 when ("expert_needs_information" OR "admin_needs_information") then "E_Waiting"
 when "seller_answered_to_expert_question" then "G_Needs_Attn"
 when "ready_to_be_sold" then "I_Ready"
 else "wtf"
end as Status

FROM
appraisal
INNER JOIN appraisal_steps ON appraisal.`status` = appraisal_steps.step_id
WHERE
appraisal.powersale = 0
GROUP BY
DATE(appraisal.created_time),
appraisal_steps.title
ORDER BY
appraisal_steps.title DESC, status

The result is correct for some of the status (is the plural stati? :) but seller_answered_to_expert_question is converted to "E_Waiting", which is incorrect.

If I change the order of the "When" clauses, different stati work and don't work.

What am I doing wrong?

Upvotes: 0

Views: 101

Answers (1)

Hart CO
Hart CO

Reputation: 34784

The problem is that a CASE statement formatted like CASE field WHEN criteria THEN END doesn't allow for multiple criteria via OR or AND so the second criteria in that WHEN line isn't being compared to the value in your title field, nothing is getting bucketed in "G_Needs_Attn", "I_Ready", or "wtf" in this case.

You can fix this in a couple ways:

split your OR line into two:

 when "expert_needs_information" then "E_Waiting"
 when "admin_needs_information" then "E_Waiting"

Or use this format of CASE statement:

    SELECT
    DATE(appraisal.created_time),
    appraisal_steps.title,
    Count(appraisal.appraisal_id),
    case when appraisal_steps.title = "archive" then "A_Archived"
     when appraisal_steps.title = "on_sale" then "J_Selling"
     when appraisal_steps.title = "under_evaluation" then "F_Evaluating"
     when appraisal_steps.title = "evaluated" then "H_Appraisal Complete"
     when appraisal_steps.title = "pending" then "B_Pending"
     when appraisal_steps.title = "crap" then "C_Not For Lofty"
     when appraisal_steps.title = "waiting_internal_expert_evaluation" then "D_Unclaimed"
     when appraisal_steps.title = "expert_needs_information" OR appraisal_steps.title = "admin_needs_information" then "E_Waiting"
     when appraisal_steps.title = "seller_answered_to_expert_question" then "G_Needs_Attn"
     when appraisal_steps.title = "ready_to_be_sold" then "I_Ready"
     else "wtf"
    end as Status       
    FROM
    appraisal
    INNER JOIN appraisal_steps ON appraisal.`status` = appraisal_steps.step_id
    WHERE
    appraisal.powersale = 0
    GROUP BY
    DATE(appraisal.created_time),
    appraisal_steps.title
    ORDER BY
    appraisal_steps.title DESC, status

This format allows for criteria on multiple fields to be evaluated, or multiple criteria for the same field. You could also use when appraisal_steps.title IN ("expert_needs_information","admin_needs_information") then "E_Waiting" for that line.

Here's a demo showing how the errant OR ends up as a catch-all: SQL Fiddle

Upvotes: 2

Related Questions