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.....);
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;