Using decode in where clause

Question

I have a DECODE in my WHERE clause like this:

Where id = decode('&flag','Yes',(Select id from tab where id > 10),0)

This code works if the subquery returns one post. If I returns several I get an error like, ORA-01427, "Single-row subquery returns more than one row"

I've tried to change the '=' to an 'in' but I still get the same error!

Any ideas?

extended example:

WHERE Dop_id = (DECODE ('&prep_flag', 'Yes', 
    (SELECT Dop_id FROM 
                   ( SELECT DOP_id, name FROM TABLE)
                    WHERE name IS NOT NULL) 
 , Dop_id))

as mention this works if the select statmen returns on row, and not several.

Solution

Assuming decode is only expecting a single value where you have Select id from tab where id > 10 I would try moving the select outside of decode:

WHERE id IN (
    SELECT decode('&flag', 'Yes', id, 0)
    FROM tab
    WHERE id > 10
)

Comments

[wweicker]

This is not possible with = or with IN because DECODE() can't return a set as a single result. You'll have to change your design, or use SELECT MAX(id)... to limit your set to a single result.

[Ishamael]

I can see why i should do it that way you have recomended. But i would like to use the decode on the whare clause. if the flag is Yes then i want all the id where the column name is not null.

[Ishamael]

i've added an END in the Case clause. But then i've get the same error as before. ORA-01427 (Single-row subquery returns more than one row)