Oracle SQL: select from different tables from the result of a variable-CodePudding

Hi,
I need to create a select with a condition for the from clause: if the variable is equal to POS the select must be done with table tb_a and if the variable is equal to PRE the selected will be done with the table tb_b.
I tried both ways below, but neither worked.

SELECT * FROM tb_a WHERE '&prod' = 'POS'
UNION ALL 
SELECT * FROM tb_b WHERE '&prod' = 'PRE';

SELECT * 
  FROM
 (CASE '&prod'
  WHEN 'prod' = 'POS' THEN tb_a
  WHEN 'prod' = 'PRE' THEN tb_b
  END);

Can you help me please?

CodePudding user response：

Of course it works.

SQL> create table tb_a as select * from dept where deptno in (10, 20);  --> for POS

Table created.

SQL> create table tb_b as select * from dept where deptno in (30, 40);  --> for PRE

Table created.

SQL> select * from tb_a where '&&prod' = 'POS'
  2  union all
  3  select * from tb_b where '&&prod' = 'PRE';
Enter value for prod: POS

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

SQL> undefine prod
SQL> /
Enter value for prod: PRE

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

SQL>

CodePudding user response：

You can create a function (or a procedure) which returns a record set of SYS_REFCURSOR type parameter after accepting one of your parameters (POS,PRE) no matter what structure has your tables(tb_a or tb_b) such as

CREATE OR REPLACE FUNCTION Get_tab_ab( i_prod VARCHAR2 ) RETURN SYS_REFCURSOR IS
  v_recordset  SYS_REFCURSOR;                     
  v_sql        VARCHAR2(32767);
BEGIN
  v_sql := 'SELECT * FROM '||CASE 
                             WHEN i_prod = 'POS' THEN 
                                  'tb_a' 
                             WHEN i_prod = 'PRE' THEN 
                                  'tb_b' 
                              END;
  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

and then call from SQL Developer's console like this

SQL> DECLARE
    res SYS_REFCURSOR;
BEGIN
   :res := Get_tab_ab('POS');
END;
/

SQL> PRINT res;

or you can use a common ROWTYPE variable because the structures of the tables(tb_a or tb_b) are identical considering you are combining them with a UNION ALL operator along with FETCH..INTO clause. This time you'll directly print out the desired columns without creating a stored object such as a function or procedure, just switching the value of the variable v_prod

DECLARE
  v_recordset  SYS_REFCURSOR;
  v_rec        tb_a%ROWTYPE;
  v_sql        VARCHAR2(32767);
  v_prod       VARCHAR2(3) := 'POS';
BEGIN

  v_sql := 'SELECT * FROM '||CASE 
                             WHEN v_prod = 'POS' THEN 
                                  'tb_a' 
                             WHEN v_prod = 'PRE' THEN 
                                  'tb_b' 
                              END;
  OPEN v_recordset FOR v_sql;
  LOOP
     FETCH v_recordset INTO v_rec;
    EXIT WHEN v_recordset%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.col1||' '||v_rec.col2||' '||v_rec.col3);
  END LOOP;
  CLOSE v_recordset;
  
END;
/