CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Tom AL
Tom AL

Reputation: 57

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


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. The number of columns in the tables is different.

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?

Upvotes: 1

Views: 1366

Answers (3)

Jon Heller
Jon Heller

Reputation: 36922

If the logic of choosing the table must be done in the SQL script, you need to pass in the PRE or POS to the script, use a bind variable to determine the table name, convert the bind variable into a substitution variable, and then select from the substitution variable. (It seems like there ought to be a simpler way to do this.)

--Pass in either "PRE" or "POS" to this script, and it will select from the appropriate table.


--AVoid the "old" and "new" lines when variables are substituted.
set verify off;

--Create a bind variable and a substitution variable.
variable table_name_bind varchar2(128);
column table_name_column new_value table_name_substitution_variable noprint

--Assign the bind variable based on the input argument.
begin
    if '&1' = 'POS' then
        :table_name_bind := 'tb_a';
    elsif '&1' = 'PRE' then
        :table_name_bind := 'tb_b';
    else
        raise_application_error(-20000, 'Unexpected input value &1');
    end if;
end;
/

--Convert the bind variable into the substitution variable through a SQL statement.
select :table_name_bind table_name_column from dual;

--Select from the substitution variable:
select * from &table_name_substitution_variable;

exit;

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

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;
/

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143163

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>

Upvotes: 1

Related Questions