Using IN outside CASE Statement after WHERE Clause i get SQL Error [1427] [21000]: ORA-01427: single-CodePudding

I have below query whereby i want to select all records at a specific hour but the other hours i want to filter to whitelisted records

SELECT
    *
FROM
    MY_TABLE
WHERE
    COLUMN_A IN
    (CASE
        WHEN TO_CHAR(COL_TIMESTAMP, 'YYYYMMDDHH24') != '2021111217' THEN (
            SELECT DISTINCT COLUMN_A
        FROM
            ANOTHER_TABLE )
        ELSE COLUMN_A 
    END);

However with the query i get error

SQL Error [1427] [21000]: ORA-01427: single-row subquery returns more than one row

How do i write this query without using union

CodePudding user response：

Use AND and OR:

SELECT *
FROM   MY_TABLE
WHERE  (TO_CHAR(COL_TIMESTAMP, 'YYYYMMDDHH24') != '2021111217'
        AND COLUMN_A IN (SELECT DISTINCT COLUMN_A FROM ANOTHER_TABLE))
OR     TO_CHAR(COL_TIMESTAMP, 'YYYYMMDDHH24') = '2021111217'
OR     col_timestamp IS NULL;

The IS NULL filter is necessary as != does not return false for NULL != '2021111217' so to match your original logic you need to include the extra filter.

CodePudding user response：

Rewrite it to

select * 
from my_table a
where a.column_a in 
  (select case when to_char(a.col_timestamp, 'yyyymmddhh24') <> '2021111217' then b.column_a
               else a.column_a
          end
     from another_table b
   )

As MT0 commented, that won't really work if another_table is empty, but this should:

select * 
from my_table a
where a.column_a in 
  (select b.column_a
     from another_table b
     where to_char(a.col_timestamp, 'yyyymmddhh24') <> '2021111217'
   union
   select a.column_a 
     from dual
     where to_char(a.col_timestamp, 'yyyymmddhh24') =  '2021111217'
   );