Oracle SQL：在'WITH'语句中，如何进行条件选择，使用IF还是CASE？(Oracle SQL: in 'WITH' statement, how to do conditional select, use IF or CASE?)

 我有两张桌子： 
 
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;
 
 但后来我无法弄清楚如何对后面的'WITH'进行条件选择： 
 
------------------------------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%';
 
 最终，我能以某种方式推广这个吗？ 即在SQL（至少oracle）哪里可以断言条件语句，哪里不能？ 

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? 

原文：https://stackoverflow.com/questions/51330449