Can we return multiple values in THEN in a CASE statement in Oracle SQL?

Can we return multiple values in THEN in a CASE statement in Oracle SQL?

I want to get multiple values in the CASE's THEN statement, based on when I want to get multiple values in THEN.

select IDt.,
       t.TERM_CODE,
       t.DETAIL_CODE
from   tableName t
where  t.TERM_CODE = :term
and    t.DETAIL_CODE IN (select t.DETAIL_CODE
                         from   tableName a
                         where  case when a.TERM_CODE = '202310'
                                then a.DETAIL_CODE in ('A','B','C')
                                when a.TERM_CODE = '202320'
                                then a.DETAIL_CODE in ('D','E','F')
                                when a.TERM_CODE = '202330'
                                then a.DETAIL_CODE in ('G','H','I')
                        );

This is my query, here I want to get multiple Detail_Codes in my THEN based on the user given term code. So for each term code I have different detail codes to be taken for which I have used case when then. However I was unable to get the desired output.

Basically if user enters the term as 202320 then my query should pick second case and details codes should be ('D', 'E', 'F').

Can anyone please help me out with this or provide an alternate solution?

You cannot return an expression from the THEN clause of a CASE expression; it needs to return a single scalar value. Additionally, you have AND t.DETAIL_CODE IN (SELECT t.DETAIL_CODE FROM ... which is just like doing AND 1 IN (SELECT 1 FROM ....

Instead, use AND and OR:

select ID,
       TERM_CODE,
       DETAIL_CODE
from   tableName
where  TERM_CODE = :term
and    DETAIL_CODE IN (select DETAIL_CODE
                       from   tableName
                       where  (TERM_CODE = '202310' AND DETAIL_CODE in ('A','B','C'))
                       OR     (TERM_CODE = '202320' AND DETAIL_CODE in ('D','E','F'))
                       OR     (TERM_CODE = '202330' AND DETAIL_CODE in ('G','H','I'))
                      );

or use IN with multiple parameters:

select ID,
       TERM_CODE,
       DETAIL_CODE
from   tableName
where  TERM_CODE = :term
and    DETAIL_CODE IN (select DETAIL_CODE
                       from   tableName
                       where  (TERM_CODE, DETAIL_CODE) IN (
                                ('202310', 'A'),
                                ('202310', 'B'),
                                ('202310', 'C'),
                                ('202320', 'D'),
                                ('202320', 'E'),
                                ('202320', 'F'),
                                ('202330', 'G'),
                                ('202330', 'H'),
                                ('202330', 'I')
                              )
                      );

Maybe you could consider creating a table with detail codes instead of hardcoding the values in your query (or queries). It will keep the data that you could use from any sql or pl/sql.
For instance, if your details data table would be like below:

        ID     TERM_ID  DETAIL_CODE
----------  ----------  -----------
         1           1  A
         2           1  B
         3           1  C
         4           2  D
         5           2  E
         6           2  F
         7           3  G
         8           3  H
         9           3  I

... then along with some terms sample data...

WITH
	tbl_terms (ID, TERM_CODE) AS
		(
			Select 1, '202310'  From dual Union All
			Select 2, '202320'  From dual Union All
			Select 3, '202330'  From dual 			
		),
	tbl_term_details (ID, TERM_ID, DETAIL_CODE) AS
		(
			Select 1, 1, 'A' From Dual Union All
			Select 2, 1, 'B' From Dual Union All
			Select 3, 1, 'C' From Dual Union All
			Select 4, 2, 'D' From Dual Union All
			Select 5, 2, 'E' From Dual Union All
			Select 6, 2, 'F' From Dual Union All
			Select 7, 3, 'G' From Dual Union All
			Select 8, 3, 'H' From Dual Union All
			Select 9, 3, 'I' From Dual 
		)

... you would select it like here:

Select 		  t.ID, t.TERM_CODE, td.DETAIL_CODE
From   		  tbl_terms t
Inner Join    tbl_term_details td ON(td.TERM_ID = t.ID)
Where  		  t.TERM_CODE = :term 


Result for :term = '202310'
        ID TERM_CODE DETAIL_CODE
---------- --------- -----------
         1 202310    A
         1 202310    B
         1 202310    C

Yes, you can use objects for this including returning condition from case expression. Even with different types of parameters and algorithms using inheritance. Like this:

select * from
(
select 'A' as value , 'scalar' as check_type from dual
union all
select 'b' , 'array' from dual
) s1
where
(
case 
when s1.check_type = 'scalar' then t_test_child_scalar ( 'D' )
when s1.check_type = 'array' then t_test_child_array ( t_string_array ( 'b' , 'C' ) )
end
).check_in ( s1.value ) = t_test_result ( 'Y' , 'N' ) ;
;

Objects are created like this:

CREATE OR REPLACE TYPE t_string_array AS TABLE OF VARCHAR2 ( 32767 )
;
CREATE OR REPLACE TYPE t_test_result force AS OBJECT
( is_in varchar2 ( 1 char )
, is_capital varchar2 ( 1 char )
)
;
CREATE OR REPLACE TYPE t_test_parent force AS OBJECT 
( v_dummy number
, NOT INSTANTIABLE MEMBER FUNCTION check_in ( i_value string )
RETURN t_test_result
)
NOT INSTANTIABLE NOT FINAL
;
CREATE OR REPLACE TYPE t_test_child_scalar UNDER t_test_parent
( v_value varchar2 ( 1 char )
, CONSTRUCTOR FUNCTION t_test_child_scalar
( i_value string
)
RETURN SELF AS RESULT
, OVERRIDING MEMBER FUNCTION check_in ( i_value string )
RETURN t_test_result
)
;
CREATE OR REPLACE TYPE t_test_child_array UNDER t_test_parent
( v_array t_string_array
, CONSTRUCTOR FUNCTION t_test_child_array
( i_array t_string_array
)
RETURN SELF AS RESULT
, OVERRIDING MEMBER FUNCTION check_in ( i_value string )
RETURN t_test_result
)
;
CREATE OR REPLACE TYPE BODY t_test_child_scalar AS
CONSTRUCTOR FUNCTION t_test_child_scalar
( i_value string
)
RETURN SELF AS RESULT
IS
BEGIN
v_value := i_value ;
RETURN  ;
END ;
OVERRIDING MEMBER FUNCTION check_in ( i_value string )
RETURN t_test_result
IS
v_result t_test_result default new t_test_result ( null , null ) ;
BEGIN
if i_value = v_value then
v_result.is_in := 'Y' ;
else
v_result.is_in := 'N' ;
end if ;
if i_value = upper ( i_value ) then
v_result.is_capital := 'Y' ;
else
v_result.is_capital := 'N' ;
end if ;
return v_result ;
END check_in ;
END ;
/
CREATE OR REPLACE TYPE BODY t_test_child_array AS
CONSTRUCTOR FUNCTION t_test_child_array
( i_array t_string_array
)
RETURN SELF AS RESULT
IS
BEGIN
v_array := i_array ;
RETURN  ;
END ;
OVERRIDING MEMBER FUNCTION check_in ( i_value string )
RETURN t_test_result
IS
v_result t_test_result default new t_test_result ( null , null ) ;
BEGIN
if i_value member of v_array then
v_result.is_in := 'Y' ;
else
v_result.is_in := 'N' ;
end if ;
if i_value = upper ( i_value ) then
v_result.is_capital := 'Y' ;
else
v_result.is_capital := 'N' ;
end if ;
return v_result ;
END check_in ;
END ;
/