Using if statement to select table inside FROM statement in ORACLE

Question

please someone help me. I want to convert this query from mybatis to ORACLE

SELECT *
FROM TABLE_A
   <if paramA is not null>
     , ( SELECT *
         FROM TABLE_B
       ) B
   </if>
WHERE ....
   <if paramA is not null>
      A.key = B.key
   </if>

i'm currently stuck at that IF tag inside FROM.

Oracle's statement cannot have parameterized object names, because bindings receive the data after the parsing is complete, that means semantic checks are passed: all requested objects exist, are accessible to user, they have requested columns etc. — astentx, Apr 01 '23 at 17:38

score 2 · Accepted Answer · answered Apr 01 '23 at 17:59

Oracle does not allow you to conditionally include tables.

One option may be to always join TABLE_B and filter on the parameter (as a bind parameter) in the join condition:

SELECT *
FROM   TABLE_A A
       LEFT OUTER JOIN TABLE_B
       ON A.key = B.key AND :paramA IS NOT NULL

It is not quite the same as, when the :paramA bind variable is NULL then the TABLE_B columns will be included in the output but the values of those TABLE_B columns will all be NULL.

score 1 · Answer 2 · answered Apr 01 '23 at 18:08

You'll need to use dynamic SQL if you are programmatically determining object names. Putting dynamic SQL together and executing it simple, figuring out how you want to get the data back to your calling program or client requires a bit more thought, as there are multiple options. Here's one:

CREATE TYPE whatever_rectype IS OBJECT (col1 integer, etc..);
CREATE TYPE whatever_tabtype IS TABLE OF whatever_rectype;

CREATE OR REPLACE FUNCTION whatever (paramA IN varchar2)
RETURN whatever_tabtype PIPELINED
AS
  var_sql varchar2(32767);
  rec_results whatever_rectype;
BEGIN
  var_sql := '
SELECT *
  FROM TABLE_A A';

  IF paramA IS NOT NULL
  THEN
    var_sql := var_sql ||', 
    (SELECT whatever_rectype(col1,col2,etc..)
       FROM TABLE_B
     ) B';
  END IF;

  var_sql := var_sql || '
 WHERE 1=1';

  IF paramA IS NOT NULL
  THEN
    var_sql := var_sql ||'
   AND A.key = B.key';
  END IF;

  OPEN cur_whatever FOR var_sql;

  FETCH cur_whatever INTO rec_results;

  WHILE cur_whatever%FOUND
  LOOP
    PIPE ROW(rec_results);
    FETCH cur_whatever INTO rec_results;
  END LOOP;
END;

To execute:

SELECT * 
  FROM TABLE(whatever('myparamAValue'))

In recent versions you can dispense with the TABLE keyword:

SELECT * 
  FROM whatever('myparamAValue')

Using if statement to select table inside FROM statement in ORACLE

2 Answers2