ORA-01427 Single row subquery returns more than 1 row

This error seems to be popular and there are many related answers. However, the existing answers do not seem to apply to my situation.

I am simplifying my case using 2 tables: Test1 and Test3 (see illustration)

What I am trying to do is attempting to find the records in test3 table that does not match the value in field value1 (if the field check_condition1 = 1 if it is 0 then I do not care)

so basically the result should be similar to this query in this particular scenario:

select distinct t3.* from test3 t3, test1 t1
where t3.department=t1.department
and t3.value1 not in ('A','B');

However, if I use this statement:

select distinct t3.* from test3 t3, test1 t1
where t3.department=t1.department
and t3.value1 not in 
(
  case t1.CHECK_CONDITION1
  when 0 then
    (select '1' from dual where 1=2)
  when 1 then
     ( select value1 from test1 where department=t3.DEPARTMENT)
  end
)

I got this message:

ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

I thought my subquery “select value1 from test1 where department=t3.DEPARTMENT” should return a set for t3.value1 to check against.

How should the statement be corrected? My goal is to use Test1 table as a control table, the fields Check_condition1, check_condition2 are the “switches” that could be turn on and off without having to change the main query. Please advise if my thought make sense.

Attached are the script to create the tables test1 and test3 for easier duplication of my issue.

  CREATE TABLE "TEST1" 
   (    "DEPARTMENT" NUMBER(3,0), 
    "VALUE1" VARCHAR2(26 BYTE), 
    "VALUE2" VARCHAR2(26 BYTE), 
    "CHECK_CONDITION1" NUMBER(3,0), 
    "CHECK_CONDITION2" NUMBER(3,0)
   ) 
Insert into TEST1 (DEPARTMENT,VALUE1,VALUE2,CHECK_CONDITION1,CHECK_CONDITION2) values (1,'A','Z',1,0);
Insert into TEST1 (DEPARTMENT,VALUE1,VALUE2,CHECK_CONDITION1,CHECK_CONDITION2) values (1,'B','Y',1,0);

  CREATE TABLE "TEST3" 
   (    "DEPARTMENT" NUMBER(3,0), 
    "VALUE1" VARCHAR2(26 BYTE), 
    "VALUE2" VARCHAR2(26 BYTE), 
    "VALUE3" VARCHAR2(26 BYTE)
   );
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'A','T','Whatever');
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'Z','Y','Whatever');
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'B','Y','Whatever');

Answer

From the CASE expression documentation:

For both simple and searched CASE expressions, all of the return_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all return expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

The return_expr of a CASE statement expects a single value so your sub-query:

( select value1 from test1 where department=t3.DEPARTMENT)

is what is raising the exception.

Instead use the filter on the sub-query:

select distinct t3.*
from   test3 t3
       INNER JOIN test1 t1
       ON ( t3.department=t1.department )
WHERE  t3.value1 not in (
         select value1
         from   test1
         where  department=t3.DEPARTMENT
         AND    t1.CHECK_CONDITION1 = 1
       )

Which, for your test data, outputs:

DEPARTMENT | VALUE1 | VALUE2 | VALUE3  
---------: | :----- | :----- | :-------
         1 | Z      | Y      | Whatever

db<>fiddle here