CODEWITHSUNDEEP
mysqlsql
Spredzy
Spredzy

Reputation: 5164

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,

Upvotes: 1

Views: 2976

Answers (2)

Curtis
Curtis

Reputation: 103358

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;

Upvotes: 0

gbn
gbn

Reputation: 432260

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;

Upvotes: 3

Related Questions