Reputation: 57
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
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
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
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