When the datawindow syntax beginning with the non-select key words syntax, Appeon will resolves to execute Procedure, leading to implementation failure. Such as below data window sql syntax.
with tmp as(select emp_id from employees )
select a.emp_id from employees a, tmp b
where a. emp_id = b. emp_id
and rownum < 5;
end pro_with_test;
Workaround:
Move datawindow sql to a stored procedure and then create datewindow to use that stored procedure as data source to work around this issue.
CREATE OR REPLACE PACKAGE ods is
type rec_set is ref cursor;
end ods;
CREATE OR REPLACE PROCEDURE pro_with_test(rcl out ods.rec_set)
is
begin
open rcl for
with tmp as(select emp_id from employees )
select a.emp_id from employees a, tmp b
where a. emp_id = b. emp_id
and rownum < 5;
end pro_with_test;