Ted - 10 months ago 45

MySQL Question

I have a variable

`myvalue`

`"TRUE"`

`"TRUE,FALSE"`

When

`myvalue = "TRUE"`

`N/A`

`myvalue = "TRUE,FALSE"`

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 Source

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.