oracle-databaseif-statementplsqlsyntaxsubquery-factoring

PL/SQL: Use 'IF' statement outside 'WITH ... AS' clause


I am trying to write a procedure where I use Subquery Factoring 'WITH .. AS', but when I use 'IF .. THEN' before it, I get syntax error, I don't know how should I write it, any help?

  BEGIN
  OPEN my_SYS_REFCURSOR FOR
   IF .. IS NULL
   THEN
     WITH SomeName
          AS (SELECT.....);

Solution

  • You simply need to separate the IF statement from the OPEN:

    declare
        my_sys_refcursor sys_refcursor;
    begin
        if (1=1) then /* condition satisfied, cursor from some table */
            open my_sys_refcursor for
            with somename as ( select '1' as one from dual)
            select one
            from somename;
        else  /* condition not satisfied, select from different tables */
            open my_sys_refcursor for
            with someOthername as ( select 'one' as one from dual)
            select one
            from someOthername;
        end if;
    end;