I have code in my Oracle stored procedure like this:

SELECT COUNT(EMP_ID) INTO A_VARIABLE 
FROM STUDENT;

IF (A_VARIABLE > 0) THEN
   SELECT *  FROM DEPARTMENT1;
ELSE
   SELECT * FROM DEPARTMENT2;
END IF;

Basically I have to do this same filtration of rows in my SQL query (and not in the procedure or script).

Can anyone help with this Oracle query?

I have tried multiple solutions, but I'm not getting the desired output.

CodePudding user response：

Sample student table; depending on course students take, main query will return data from one table (or another).

SQL> select * from student;

    ID NAME   COURSE

---

     1 Little Web
     2 Foot   Web

SQL> select * from department1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

SQL> select * from department2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Query uses a CTE that counts rows in student table; it is then used in exists subquery for two similar select statements which fetch rows from one of departments tables.

Currently, nobody is in IT so departments2 table it is:

SQL> with temp as
  2    (select count(*) cnt
  3     from student
  4     where course = 'IT')
  5  --
  6  select * from department1
  7  where exists (select null from temp
  8                where cnt > 0
  9               )
 10  union all
 11  select * from department2
 12  where exists (select null from temp
 13                where cnt = 0
 14               );

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

However, if someone studies IT, department1 will provide data:

SQL> update student set course = 'IT' where id = 1;

1 row updated.

SQL> select * from student;

        ID NAME   COURSE
---------- ------ ----------
         1 Little IT
         2 Foot   Web

SQL> with temp as
  2    (select count(*) cnt
  3     from student
  4     where course = 'IT')
  5  --
  6  select * from department1
  7  where exists (select null from temp
  8                where cnt > 0
  9               )
 10  union all
 11  select * from department2
 12  where exists (select null from temp
 13                where cnt = 0
 14               );

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

SQL>

CodePudding user response：

For 19c (19.7 and above) you may use SQL_MACRO(table) feature and inline function declaration.

But different output structure of the same query is somewhat misleading, so I would recommend to use union all approach and align columns of two tables.

Below is an example for SQL Macro.

Setup:

create table table1
as
select
  level as id,
  level as val
from dual
connect by level < 5

create table table2
as
select
  level as id,
  lpad(level, 4, '0') as val,
  dbms_random.string('x', 5) as str2
from dual
connect by level < 4

create table decision
as
select 'Y' as res
from dual

Run 1:

with function f_decide_table
return varchar2
sql_macro(table)
as
  l_res varchar2(100);
begin
  select
    coalesce (
      max(q'[select * from table1]'),
      q'[select * from table2]'
    )

    into l_res
  from decision;

  return l_res;
end;

select *
from f_decide_table()
order by 1

ID	VAL
1	1
2	2
3	3
4	4

Run 2:

truncate table decision

with function f_decide_table
return varchar2
sql_macro(table)
as
  l_res varchar2(100);
begin
  select
    coalesce (
      max(q'[select * from table1]'),
      q'[select * from table2]'
    )

    into l_res
  from decision;

  return l_res;
end;

select *
from f_decide_table()
order by 1

ID	VAL	STR2
1	0001	DGNY9
2	0002	UHFYH
3	0003	EU12B

fiddle