Running select statement in Oracle Apex Error

Question

I don't know everything about Oracle Apex yet, but When I run this code in Oracle developer it runs just as fine, but when I am using Oracle Apex and making an application through an sql query it gives me the error:

• Query cannot be parsed, please check the syntax of your query.

with x as (
select count(cou_code) as changes, state_code from sdrp15_submission_log sl
where state_code in (select distinct state_code from sdrp15_submission_log
 where state_code = sl.state_code
 and cou_code != 'All')
and qa_date is null
and phase = 'A' 
group by state_code)
, y as (select count(cou_code) as cnt, st_code
from sdrp15_cosd
where st_code = (select distinct state_code 
 from sdrp15_submission_log
 where state_code = st_code
 and cou_code = 'All'
 and phase = 'A'
 and qa_date is null)
and phase = 'A'
group by st_code)
select x.state_code, x.changes+y.cnt
from x join y on x.state_code = Y.st_code

• Sentinel: Is there more to the error message than "Query cannot be parsed, please check the syntax of your query"?
• Alfred H.: It says" table or view does not exist, but that doesn't make sense since it runs in my sql developer.

Answer 1

Check the parsing schema of your APEX application. The tables and are probabaly owned by a schema other than the default parsing schema of your APEX application.

If the parsing schema of your APEX application has the required privileges to access the required tables and views, you may be able to get it working by prepending the objects owner schema name to the object for example: My_Table would become SomeSchema.My_Table.

Or you could create synonyms to the tables and views in the parsing schema referencing the objects in the owners schema:

create synonym My_Table for SomeSchema.My_Table;