IF (CASE) THEN SELECT STATEMENT in Oracle SQL

Question

I want to create a query in oracle that looks like this

DECLARE
   my_count NUMBER;
BEGIN
  SELECT COUNT(table.column1) INTO my_count FROM table;

  IF my_count > 2 THEN
     SELECT * FROM table WHERE table.column2 = 'ABC';
  ELSE
     SELECT * FROM table WHERE table.column2 != 'ABC';
  END IF;
END;

Is there any answer that works the same?

Answer 1

Something like this?

with temp (cnt) as
  (select count(column1) from table)
select * from table cross join temp where column2 = 'ABC' and cnt > 2
union all
select * from table cross join temp where column2 <> 'ABC' and cnt <= 2;

---

Demo, based on Scott's sample schema: there are 4 clerks and - as that number is greater than 2, I'll fetch employees who work in department 10 (i.e. query in line #3 will be executed):

SQL> select count(*) from emp where job = 'CLERK';

  COUNT(*)
----------
         4

SQL> select ename from emp where deptno = 10;

ENAME
----------
CLARK
KING
MILLER
    
SQL> with temp (cnt) as
  2    (select count(*) from emp where job = 'CLERK')
  3  select ename from emp cross join temp where deptno = 10 and cnt > 2
  4  union all
  5  select ename from emp cross join temp where deptno <>10 and cnt <= 2;

ENAME
----------
CLARK             --> right; here they are
KING
MILLER

As there's only one president, count is lower than 2 and query will return results from line #5:

SQL> with temp (cnt) as
  2    (select count(*) from emp where job = 'PRESIDENT')
  3  select ename from emp cross join temp where deptno = 10 and cnt > 2
  4  union all
  5  select ename from emp cross join temp where deptno <>10 and cnt <= 2;

ENAME
----------
BLAKE
SCOTT
TURNER
ADAMS
JAMES
FORD
SMITH
ALLEN
WARD
JONES
MARTIN

11 rows selected.

SQL>