Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Changing form SQL to multi table join...

V RickertMay 14 2013 — edited May 14 2013
I need to be able to join tables in the detail section of my master detail form. Since I have more than three tables to join, I think APEX needs to use "ROWID" to track the correct key value of the primary table. When I try to substitute the basic generated sql statement for the report to my own joined table I get a "CANNOT PARSE" error statement. If I remove "ROWID" from the statement then everything is fine. Here is the apex generated statement:

select
"ROWID",
"PO_ID",
"PO_DET_ID",
"CHANGE_ORDER",
"START_DATE",
"END_DATE",
"PO_DET_AMT",
"SCOPE",
"PO_STATUS",
"WBS"
from "#OWNER#"."PO_DETAILS"
where "PO_ID" = :P240_PO_ID

However, I need for the statement to read:

select "ROWID",
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.po_status,
po_details.wbs
from PO_DETAILS,PO,WBS,PROJECT
where po_details.po_id = po.po_id
and po.po_number = wbs.po_number_id
and po_details.wbs = wbs.wbs_number_id
and substr(wbs.wbs_number_id,1,6) = project.wbs_sequence
and project.project_number = nvl(:F101_FPC_NUMBER,project.project_number)
and po_details.po_id = :P240_PO_ID

I then get the following error from ORACLE:

•Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic columns'' checkbox below the region source to proceed without parsing. ORA-00918: column ambiguously defined

I'm guessing the "ambiguously defined" column is ROWID.

The SELECT aspect of this query works fine if I remove ROWID, but I then cannot update the table when the user needs to change data. I get a user-defined exception.

I'm new to APEX so I'm sure there is some aspect that I am missing.
Any hints?
This post has been answered by InoL on May 14 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 11 2013
Added on May 14 2013
7 comments
1,786 views