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-01开发者_如何学Python427: 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?

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

You have 3 sub-queries.

1. select cast(SEVERITY as varchar2(255)) name from SURV_TRIGGER_SEVERITY_LIST
2. select cast(name as varchar2(255)) name from Surv_List.groups
3. select cast(name as varchar2(255)) name from tn_tree

Each one must return 0 or 1 rows but not more.

No. Your subquery should return only one value (only one row and one column) since you'll display it on a single row.

Since you are displaying the value as one column using your query above, it looks like your intention is to get only one value.

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 there a where missing that links this ID to say a Severity list? Usually queries like this would have a condition in the subquery.. something like..

 select
     CASE WHEN action_type like 'Trigger Severity' 
             THEN (select cast(SEVERITY as varchar2(255)) name 
                      **from SURV_TRIGGER_SEVERITY_LIST trglst
                      where trglst.name = lst.severity_name**
     -----
     ---

         END display_value
     from surv_action_type_list lst
    where id = 0