Changing form SQL to multi table join...
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?