Oracle SQL: in 'WITH' statement, how to do conditional select, use IF or CASE?

Question

I have two tables:

CREATE TABLE Test_Persons_A (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(11,'LN_A1','FN_A1');

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(12,'LN_A2','FN_A2');

CREATE TABLE Test_Persons_B (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(21,'LN_B1','FN_B1');

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(22,'LN_B2','FN_B2');

commit;

But then I can't figure out how to do a conditional select for the 'WITH' that follows:

------------------------------use IF, not working ------------------------------
var TEST_TBL varchar2(20);
exec :TEST_TBL := 'test_person_A';

with Test_tbl as
(
    IF UPPER(:TEST_TBL) = 'TEST_PERSONS_A' then select * from Test_Persons_A;
    ELSIF UPPER(:TEST_TBL) = 'TEST_PERSONS_B' then select * from Test_Persons_B;
    End if;
)

select PersonID as PID, LastName as LN, FirstName as FN
    from Test_tbl tp
    where tp.LASTNAME like '%1%'

------------------------------use CASE, not working ------------------------------
var TEST_TBL varchar2(20);
exec :TEST_TBL := 'test_person_A';

with Test_tbl as
(
    CASE WHEN UPPER(:TEST_TBL) = 'TEST_PERSONS_A' then 
        select * from Test_Persons_A;
    ELSE 
        select * from Test_Persons_B;
    End
)
select PersonID as PID, LastName as LN, FirstName as FN
    from Test_tbl tp
    where tp.LASTNAME like '%1%';

And ultimately, can I generalize this somehow? i.e. In SQL (oracle at least) where can I assert conditional statements, and where can't I?

Gordon Linoff · Accepted Answer · 2018-07-14T02:14:22.810

3

Use union all:

with Test_tbl as (
      select *
      from Test_Persons_A
      where upper(:TEST_TBL) = 'TEST_PERSONS_A' 
      union all
      select *
      from Test_Persons_B
      where UPPER(:TEST_TBL) = 'TEST_PERSONS_B'
     )

This assumes the tables have the same columns in the same order.

If they don't have the same columns, list the specific columns you want for the remaining code, perhaps assigning NULL to columns that might not be in one of the tables.

edited Jul 14 '18 at 02:14

answered Jul 13 '18 at 18:00

Gordon Linoff

1,242,037
58
646
786

Thanks @Gordon Linoff, what if the two tables do not match in columns exactly? – Stochastika Jul 13 '18 at 18:05
2

If that is the case, then you have to select a matching set of columns from each table. Don't forget that you can always create a table with a null column as such `NULL AS somecolumn` – Zerodf Jul 13 '18 at 18:06
Thanks @Gordon Linoff, again. upvoted the comment. But one more problem here: if I have more complex WHERE clause here to start with, then no matter where I add the 'AND UPPER(:TEST_TBL) = ... ', the Execution Plan can not guarantee it to be evaluated first, can it? If not, there may be potentially large amount of waste by running on other conditions first of that WHERE clause? – Stochastika Jul 18 '18 at 21:51
@Stochastika . . . The optimizer should be evaluating constant expressions and doing its best. There may be situations where it misses what should be obvious, though. – Gordon Linoff Jul 19 '18 at 03:06

Oracle SQL: in 'WITH' statement, how to do conditional select, use IF or CASE?

1 Answers1

Linked