Geek Answers Handbook

Sql query with a case when returning more than one row

I am trying to make a request with a case where the condition is to see which list I will show, but I have this error ORA-01427: single-row subquery returns more than one row .

the request is:

 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

can I call a query with a lot of one line inside the case condition?

+4
source share
3 answers

I would do it in a few steps. Get the type of action, and then run the appropriate query. Whether you have this logic in front or in a stored procedure, you and probably depend on many other things.

If you absolutely need to do this, you can 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
+2
source

You have 3 subqueries.

 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 should return 0 or 1 rows, but no more.

0
source

No. Your subquery should return only one value (only one row and one column), since you will display it on one row.

Since you are displaying the value as a single column using your query above, it looks like you intend 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 where there is no link to this identifier to say a severity list? Typically, such queries will 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
0
source

More articles:


All Articles