Using Apex 18.2.0.00.12
I have a interactive grid that is based on a sql query joining 2 tables together. The column is the primary key in ORG_STRUCTURE.Org_Struct_Id and is a foreign key STAFF.Org_Struct_Id pointing at that primary key.
SELECT Staff_Id (hidden surrogate key)
, First_Name
, Middle_Name
, Suffix_Name
, Last_Name
, Title
, Phone
, Email
, Org_Struct_Id (hidden)
, Org_Name
FROM STAFF JOIN ORG_STRUCTURE USING (Org_Struct_Id)
This works fine until I made the IG editable. So what I wanted to do is when a persone either edit's or adds a new record in this IG, the column ORG_STRUCTURE.Org_Name becomes a drop down showing the available values since it is unique in ORG_STRUCTURE. I cannot get this column to become a drop down, I assume which should be of type 'select list'.
For the column in IG of Org_Name I have the 'List of Values' section like this:
LIST OF VALUES
Type: Sql query
Sql query:
SELECT Org_Name AS display
, Org_Struct_Id AS r
FROM ORG_STRUCTURE
WHERE Org_Struct_Id = :ORG_STRUCT_ID OR :ORG_STRUCT_ID IS NULL
Cascading LOV Parent Column(s): ORG_STRUCT_ID *** NOTE: I have also tried STAFF_ID here also
Items to Submit:
Optimizer Refresh: NO
This generates:
Rewrite SQL to: select a.* from (SELECT Org_Name AS display
, Org_Struct_Id AS r
FROM ORG_STRUCTURE
WHERE Org_Struct_Id = :ORG_STRUCT_ID OR :ORG_STRUCT_ID IS NULL
)a
where "R" = :p$_search_string
and ROWNUM <= :p$_max_rows
Exception in "Staff Members":
Error Stack: ORA-01403: no data found
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2103
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2093
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2821
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2066
ORA-06512: at "APEX_180200.WWV_FLOW_INTERACTIVE_GRID", line 3309
ORA-06512: at "APEX_180200.WWV_FLOW_INTERACTIVE_GRID", line 3583
Backtrace: ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2103
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2093
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2821
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2066
ORA-06512: at "APEX_180200.WWV_FLOW_INTERACTIVE_GRID", line 3309
ORA-06512: at "APEX_180200.WWV_FLOW_INTERACTIVE_GRID", line 3583
ORA-06512: at "APEX_180200.WWV_FLOW_INTERACTIVE_GRID", line 4021
I have even tried to just produce a list of values for Org_Name by changing LIST OF VALUES query to:
Sql query:
SELECT Org_Name AS display
, Org_Struct_Id AS r
FROM ORG_STRUCTURE
but this generates this:
Rewrite SQL to: select a.* from (select q.*,count(*) over () "APEX$TOTAL_ROW_COUNT"
from(select q.*,"l3817542628722040"."DISPLAY" "APEX$L3817542628722040"
from(select "STAFF_ID","FIRST_NAME","MIDDLE_NAME","LAST_NAME","SUFFIX_NAME","TITLE","PHONE","EMAIL","ORG_NAME","ORG_STRUCT_ID"
from(SELECT Staff_Id
, First_Name
, Middle_Name
, Suffix_Name
, Last_Name
, Title
, Phone
, Email
, Org_Name
, Org_Struct_Id
FROM STAFF JOIN ORG_STRUCTURE USING (Org_Struct_Id)
)q
)q,
(SELECT Org_Name AS display
, Org_Struct_Id AS r
FROM ORG_STRUCTURE
)"l3817542628722040"
where "l3817542628722040"."R"(+)=q."ORG_NAME"
)q
)a
where ROWNUM <= :p$_max_rows
Exception in "Staff Members":
Error Stack: ORA-01722: invalid number
ORA-06512: at "APEX_180200.WWV_FLOW_DYNAMIC_EXEC", line 1176
ORA-06512: at "SYS.DBMS_SQL", line 1726
ORA-06512: at "APEX_180200.WWV_FLOW_DYNAMIC_EXEC", line 1172
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2741
Backtrace: ORA-06512: at "APEX_180200.WWV_FLOW_DYNAMIC_EXEC", line 1176
ORA-06512: at "SYS.DBMS_SQL", line 1726
ORA-06512: at "APEX_180200.WWV_FLOW_DYNAMIC_EXEC", line 1172
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2741
ORA-06512: at "APEX_180200.WWV_FLOW_INTERACTIVE_GRID", line 4005
This:
"l3817542628722040"."R"(+)=q."ORG_NAME"
would generate that error since the Org_Name is a VARCHAR and the "R" in this is a NUMBER (surrogate key)
This does not work! I have done this in previous versions of apex but cannot figure out how to get this to work in this version. This has to be possible!
Is this possible?
Thanks in advance