Select list of values from case statement

Question

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 1

Edited: Instead of the UNION chaining, do:

SELECT a FROM (
  SELECT 'N/A' AS a, 'TRUE' AS b, @a := -1
  UNION
  SELECT @a := @a +1 AS a, 'TRUE,FALSE' AS b, NULL FROM information_schema.PARAMETERS LIMIT 24
) t
WHERE t.b = @myValue;

using any table in your database guaranteed to have at least 24 records.

And with MySQL, you can drop the FROM DUAL

Answer 2

CASE return an expresion not a SELECT you cant return an UNION

THIS IS OK

SELECT CASE WHEN 1=2
            THEN '2'
            ELSE (SELECT 1 as d FROM DUAL)
       END as res
FROM DUAL;

THIS ISNT OK

SELECT CASE WHEN 1=2
            THEN '2'
            ELSE (SELECT 1 as d FROM DUAL UNION
                  SELECT 2 as d FROM DUAL 
                  )
       END as res
FROM DUAL;

You want DEMO

--SELECT @myValue := 'TRUE';
SELECT @myValue := 'TRUE,FALSE';

SELECT val
FROM (SELECT "TRUE" as parameter, "N/A" as val FROM DUAL UNION
      SELECT "TRUE,FALSE", "1" FROM DUAL UNION
      SELECT "TRUE,FALSE", "2" FROM DUAL UNION
      SELECT "TRUE,FALSE", "3" FROM DUAL UNION
      SELECT "TRUE,FALSE", "4" FROM DUAL UNION
      SELECT "TRUE,FALSE", "5" FROM DUAL UNION
      SELECT "TRUE,FALSE", "6" FROM DUAL UNION
      SELECT "TRUE,FALSE", "7" FROM DUAL UNION
      SELECT "TRUE,FALSE", "8" FROM DUAL UNION
      SELECT "TRUE,FALSE", "9" FROM DUAL UNION
      SELECT "TRUE,FALSE", "10" FROM DUAL 
     ) t
WHERE t.parameter = @myValue ;

Answer 3

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.

Answer 4

Use union all:

SELECT 'N/A' as optionz
FROM dual
WHERE myvalue = 'TRUE'
UNION ALL
SELECT *
FROM (SELECT "0" as val 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
                        ) t
FROM DUAL
WHERE myvalue <> 'TRUE';

Answer 5

Have you tried doing like this:

SELECT * 
FROM DUAL
WHERE
    [YourDataColumn] IN (SELECT
                        CASE
                            WHEN myvalue = 'TRUE' THEN 'N/A'
                            ELSE (SELECT 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)
                        END AS X)

Answer 6

I think your question is a bit strange.. but perhaps this will work (if you are working with Oracle SQL):

SELECT HOURS_OF_DAY
from
(SELECT myvalue as myvalue FROM dual) a, 
(SELECT '0' hours_of_day  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
                        ) b
WHERE myvalue = 'TRUE,FALSE'