1

I need in one Oracle PL/SQL select to switch between raw select and hierarchical select statements depending on some variable. But, cannot use If statement in select. For example, I have hierarchical statement

select a.*
from myTable a
start with a.id = varValue
connect by prior a.id = a.parentId

if some varCondition is 0. If varCondition = 1 then select should give result the same as result of statement select a.* from myTable a where a.id = varValue Something like select a.* from myTable a start with a.id = varValue connect by prior a.id = decode(varCondition, ...)

Is it possible?

dragy
  • 187
  • 5
  • 22
  • there is no PL/SQL in your question. Only plain SQL. But I don't think this is possible with plain SQL. As soon as `connect by` is part of the statement it will be a recursive one. But it might indeed be possible using PL/SQL and a function that uses dynamic SQL –  Apr 25 '17 at 12:15
  • Thanks. The problem is I need to use sql as source of Apex Interactive Report and thought I can resolve this by using modified sql statement. – dragy Apr 25 '17 at 12:21

2 Answers2

3

You can use NULL in the CONNECT BY clause to ensure it is always false when your varCondition variable is 1 and to use the hierarchy in other cases:

SELECT      *
FROM        myTable
START WITH  id = varValue
CONNECT BY  PRIOR id = DECODE( varCondition, 1, NULL, parentId )
MT0
  • 143,790
  • 11
  • 59
  • 117
1

You can do it this way:

PROCEDURE GET_RECORDS(v_action IN VARCHAR2)
IS
CURSOR get_records
IS
       IF(v_action = 'DO THIS') THEN
           SELECT * from <THIS>;
       ELSE
           SELECT * from <THAT>;
       END IF;
BEGIN
       OPEN get_records;

       FETCH get_records
       INTO v_thing;

       v_loop := 0;
       WHILE get_records%FOUND
       LOOP

           FETCH get_records
           INTO v_thing;

       END LOOP;
       CLOSE get_records;
END;

(Source: [Conditionally define a Cursor in Oracle)

Or you can use this:

declare
   SQL_Text varchar2(32760) := 'select * from dual'; --your query goes here
   SQL_Text2 varchar2(32760) := 'select * from dual'; --your query goes here
   cur sys_refcursor;
begin
   IF some_condition THEN
     open cur for SQL_Text;
   ELSE
     open cur for SQL_Text2;
   END IF;
   -- ...
end;
Community
  • 1
  • 1