Ted Ted - 1 month ago 14
MySQL Question

Select list of values from case statement

I have a variable

myvalue
that is a string parameter with either the value
"TRUE"
or
"TRUE,FALSE"
.

When
myvalue = "TRUE"
I want to select just
N/A
. When
myvalue = "TRUE,FALSE"
, I want to select a list of values from 0 to 23 (representing discrete hours of the day) with each value its own row.

Here's what I have - what am I missing here? I want to be able to do this without storing anything in a table (temp or real). I'm getting syntax errors.

SELECT
CASE myvalue
WHEN "TRUE"
THEN "N/A"
WHEN "TRUE,FALSE" THEN (SELECT "0" FROM DUAL
UNION SELECT "1" FROM DUAL
UNION SELECT "2" FROM DUAL
UNION SELECT "3" FROM DUAL
UNION SELECT "4" FROM DUAL
UNION SELECT "5" FROM DUAL
UNION SELECT "6" FROM DUAL
UNION SELECT "7" FROM DUAL
UNION SELECT "8" FROM DUAL
UNION SELECT "9" FROM DUAL
UNION SELECT "10" FROM DUAL
UNION SELECT "11" FROM DUAL
UNION SELECT "12" FROM DUAL
UNION SELECT "13" FROM DUAL
UNION SELECT "14" FROM DUAL
UNION SELECT "15" FROM DUAL
UNION SELECT "16" FROM DUAL
UNION SELECT "17" FROM DUAL
UNION SELECT "18" FROM DUAL
UNION SELECT "19" FROM DUAL
UNION SELECT "20" FROM DUAL
UNION SELECT "21" FROM DUAL
UNION SELECT "22" FROM DUAL
UNION SELECT "23" FROM DUAL
)
END as optionz
FROM
DUAL


Error message:
Error Code: 1242. Subquery returns more than 1 row

Answer

Here's what I'd try:

select x from  (select myValue as y) t join
    (select 'N/A' as x, 'TRUE' as y 
    union
    select '1' as x, "TRUE,FALSE" as y
    union
    select '2' as x, "TRUE,FALSE" as y
    /* keep adding your values here */
    ) t2
on t.y = t2.y;

This makes a join to something a bit like a temporary table, and will give you lots of rows for the 'TRUE/FALSE' case, and only one row for the 'TRUE' case, which is I think what you want.

Comments