I have a weird error...ORA-00933: SQL command not properly ended for APEX sql statement

Question

I am trying to run the following code and I get an ORA-00933 error:

<code> --CODE B
select distinct po_details.po_id,
po_details.po_det_id,
po_details.change_order,
po_details.start_date,
po_details.end_date,
po_details.po_det_amt,
po_details.scope,
po_details.name,
po_details.po_status,
po_details.wbs
from po_details,wbs,project
where po_details.po_id = :P230_PO_ID
and po_details.wbs = wbs.wbs_number_id
and wbs.po_number_id = :P230_PO_NUMBER
and substr(wbs.wbs_number_id,1,6) = project.wbs_Sequence
and project.project_number = nvl(substr(:F101_FPC_NUMBER,1,10),project.project_number))
</code>

I do not see where my SQL statement is in error.

This SQL statement (CODE B) was an attempt to streamline code for an APEX application that errors out with the another error message only when I try to refresh the screen after a custom validation. Very annoying. Originally the SQL statement (CODE A) looked like this:

<code> --CODE A
select po_details.po_id,
po_details.po_det_id,
po_details.change_order,
po_details.start_date,
po_details.end_date,
po_details.po_det_amt,
po_details.scope,
po_details.name,
po_details.po_status,
po_details.wbs
from PO_DETAILS
where po_details.po_id = :P230_PO_ID
and po_details.wbs in (select distinct wbs.wbs_number_id 
from wbs, project
where wbs.po_number_id = :P230_PO_NUMBER
and substr(wbs.wbs_number_id,1,6) = project.wbs_sequence
and project.project_number = nvl(substr(:F101_FPC_NUMBER,1,10),project.project_number))
order by po_details.change_order
</code>

The error message I receive for this CODE A sql statement in APEX is this: report error: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table ORA-06510: PL/SQL: unhandled user-defined exception

...and people told me that if I use a view (CODE B scribble prior to view creation) it would fix this refresh error-out issue. What? All these hoops just for a refresh? If I can figure out the ORA-00933 error message, I might be able to proceed. It seems I'm chasing rainbows instead of getting anywhere. Any suggestions? Thanks!

Answer 1

For your ORA-00933, you have an extra parenthesis on the last line:

and project.project_number = nvl(substr(:F101_FPC_NUMBER,1,10),project.project_number))

That should be:

and project.project_number = nvl(substr(:F101_FPC_NUMBER,1,10),project.project_number)

Your 'code A' block appears to be identical to 'code B', and also has the same extra bracket. I don't see how that can generate the error you've shown even without that, so I think you've maybe pasted the wrong code there?