PL/SQL and conditional FROM clause

Question

I have a PL/SQL procedure multiple if statements, constructing a select statements with the same select list but different where clauses. The selects only pull fields from Table2 but the rest of tables in FROM clauses are different.

Example:

SELECT t2.Column1, t2.Column2, t2.Column3
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID=t2.ID
LEFT JOIN Table3 t3 ON t3.ID=t1.Second_ID
WHERE t1.Third_ID= p_first;

else if:

SELECT t2.Column1, t2.Column2, t2.Column3
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID=t2.ID
LEFT JOIN Table4 t4 ON t2.ID=t4.ID
WHERE t2.Second_ID= p_second;

else if:

SELECT t2.Column1, t2.Column2, t2.Column3
FROM Table2 t2
WHERE t2.Second_ID= p_second;

How can I combine those three into one sql statement with conditional FROM and WHERE clauses?

Answer 1

You can create dynamic query and use execute immediate for single row fetch and cursor for multirow fetch. Sample Code is as below:

    declare
     str varchar2(3200);
     str1 varchar2(10):=1;
     v1 VARCHAR2(20);
     v2 VARCHAR2(20);
     v3 VARCHAR2(20);
     str2 varchar2(1000);

    begin

     str2:='SELECT t2.c1,t2.c2,t2.c3 FROM ';

     if str1=3 then
        str:= 't1,t2,t3 where t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=p_first ;' ;
     elsif str1=2 then
        str:=' t1,t2,t4 where t1.c1=t2.c1 and t2.c1=t4.c1 and t2.c1=p_second ;' ;
     else
        str:='t2 where t2.c1=p_second ;';
     end if;

      str2:=str2 || str;

     dbms_output.put_line(str2);
     EXECUTE IMMEDIATE  str2 into v1,v2,v3;

     dbms_output.put_line(v1||','||v2||','||v3);

    end;

Answer 2

One approach you could take is to use a cursor variable and the OPEN FOR syntax. You can use it with static SQL to simply choose between a small number of alternative SELECTS. You can also use it with a dynamically-constructed SELECT, in which case it all comes down to you writing the code to construct the variations. Hopefully the code below will make the general idea clear.

CREATE OR REPLACE PACKAGE refcursor_pkg
IS
   -- Use this REF CURSOR to declare cursor variables whose
   -- queries return data from the ALL_OBJECTS table.

   TYPE all_objects_t IS REF CURSOR
      RETURN all_objects%ROWTYPE;

   -- Use this REF CURSOR to declare cursor variables whose
   -- queries return any number of columns.

   TYPE weak_t IS REF CURSOR;

   -- Return rows in ALL_OBJECTS for any objects
   -- in the specified schema
   FUNCTION objects_in_schema_cv (
      schema_in        IN   all_objects.owner%TYPE
    , name_filter_in   IN   VARCHAR2
   )
      RETURN all_objects_t;

   -- Return data from whatever query is passed as an argument.
   FUNCTION data_from_any_query_cv (query_in IN VARCHAR2)
      RETURN weak_t;

   -- Return data from whatever query is passed as an argument.
   -- But this time, use the predefined weak type,
   -- available in Oracle9i Database Release 2 and above.
   FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2)
      RETURN sys_refcursor;
END refcursor_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcursor_pkg
IS
   /* Static SQL - note different where clause */
   FUNCTION objects_in_schema_cv (
      schema_in        IN   all_objects.owner%TYPE
    , name_filter_in   IN   VARCHAR2
   )
      RETURN all_objects_t
   IS
      l_cursor_variable   all_objects_t;
   BEGIN
      IF name_filter_in IS NULL
      THEN
         OPEN l_cursor_variable FOR
            SELECT *
              FROM all_objects
             WHERE owner = schema_in;
      ELSE
         OPEN l_cursor_variable FOR
            SELECT *
              FROM all_objects
             WHERE owner = schema_in AND object_name LIKE name_filter_in;
      END IF;

      RETURN l_cursor_variable;
   END objects_in_schema_cv;

   FUNCTION data_from_any_query_cv (query_in IN VARCHAR2)
      RETURN weak_t
   IS
      l_cursor_variable   weak_t;
   BEGIN
      OPEN l_cursor_variable FOR query_in;

      RETURN l_cursor_variable;
   END data_from_any_query_cv;

   FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2)
      RETURN sys_refcursor
   IS
      l_cursor_variable   sys_refcursor;
   BEGIN
      OPEN l_cursor_variable FOR query_in;

      RETURN l_cursor_variable;
   END data_from_any_query_cv2;
END refcursor_pkg;
/

/* Demonstrate strong ref cursor type. */

DECLARE
   l_objects   refcursor_pkg.all_objects_t;
   l_object    all_objects%ROWTYPE;
BEGIN
   l_objects := refcursor_pkg.objects_in_schema_cv (USER, '%EMP%');

   LOOP
      FETCH l_objects
       INTO l_object;

      EXIT WHEN l_objects%NOTFOUND;
      DBMS_OUTPUT.put_line (l_object.object_name);
   END LOOP;

   CLOSE l_objects;
END;
/

/* Demonstrate weak ref cursor type. */

DECLARE
   l_objects   sys_refcursor;
   l_object    all_objects%ROWTYPE;
BEGIN
   l_objects :=
      refcursor_pkg.data_from_any_query_cv2
                ('SELECT * FROM all_objects WHERE object_name LIKE ''%EMP%''');

   LOOP
      FETCH l_objects
       INTO l_object;

      EXIT WHEN l_objects%NOTFOUND;
      DBMS_OUTPUT.put_line (l_object.object_name);
   END LOOP;

   CLOSE l_objects;
END;
/