sql query with a case when returning more than one row

I’m trying to do a query with a case when condition to see what list I will show but I’m having this error ORA-01427: single-row subquery returns more than one row.

the query is this:

SELECT
CASE WHEN action_type like 'Trigger Severity' THEN (select cast(SEVERITY as varchar2(255)) name from SURV_TRIGGER_SEVERITY_LIST)
     WHEN action_type like 'Host Group'  then (select cast(name as varchar2(255)) name from Surv_List.groups)
     WHEN action_type like 'Host'  then (select cast(name as varchar2(255)) name from tn_tree)
     END display_value
FROM surv_action_type_list
WHERE id = 0

is it possible to call a query with more than one row inside a case condition?

Answer

I would do this in multiple steps. Get the action type, then issue the appropriate query. Whether you have this logic at the front end or in a stored procedure is up you and probably depends on a lot of other things.

If you absolutely needed to do it this way, then you could try something like this:

SELECT
    SQ.display_value
FROM
    surv_action_type_list SATL
INNER JOIN
    (
    SELECT
        'Trigger Severity' action_type,
        CAST(severity AS VARCHAR2(255)) display_value
    FROM
        SURV_TRIGGER_SEVERITY_LIST
    UNION ALL
    SELECT
        'Host Group' action_type,
        CAST(name AS VARCHAR2(255) display_value
    FROM
        Surv_List.groups
    UNION ALL
    SELECT
        'Host' action_type,
        CAST(name AS VARCHAR2(255) display_value
    FROM
        tn_tree
    ) SQ ON
    SQ.action_type = SATL.action_type
WHERE
    SATL.id = 0