Subquery returns more than 1 row when subselecting in a CASE WHEN statement
First query
This query works fine and the engine does not complain about it
SELECT id
FROM agencies
WHERE id IN ((SELECT id FROM agencies))
ORDER BY id;
开发者_如何学运维
Second query
This one does not work, the engine is complaining about Subquery returns more than 1 row
, when -according to me- I am doing the exact same thing when @param_1 IS NULL
SELECT
@param_1 := NULL,
SELECT id
FROM agencies
WHERE id IN (CASE WHEN @param_1 IS NULL THEN (SELECT id FROM agencies) ELSE 1 END )
ORDER BY id;
Does anybody see why the engine is complaining about the second query when it isnt for the first query ?
Thanks in advance,
CASE expects a scalar, single value. Not a record set.
SELECT id
FROM agencies
WHERE id IN (
SELECT id FROM agencies WHERE @param_1 IS NULL
UNION ALL
SELECT 1 WHERE @param_1 IS NOT NULL
)
ORDER BY id;
OR
SELECT id
FROM agencies
WHERE id IN (SELECT id FROM agencies)
AND @param_1 IS NULL
UNION ALL
SELECT id
FROM agencies
WHERE @param_1 IS NOT NULL AND id = 1
ORDER BY id;
Another choice is to use IF
IF @param_1 IS NULL
SELECT id
FROM agencies
WHERE id IN (SELECT id FROM agencies)
ORDER BY id;
ELSE
SELECT id
FROM agencies
WHERE id = 1
ORDER BY id;
Change to:
SELECT id
FROM agencies
WHERE id IN (
SELECT id
FROM agencies
WHERE @param_1 IS NULL
UNION ALL
SELECT 1 as id
WHERE @param_1 IS NOT NULL
)
ORDER BY id;
精彩评论