APEX

I have a form that currently has several fields defined as SELECT LIST, which works great when the list of items to be selected is small. However, one of the fields is a list of employees, which will be > 300, and I was thinking of using the POPUP LOV option. Mainly because of the Search capability - would like to just list those employees that are entered into the Search to show up in the list.

I have tried the POPUP LOV within a CREATE record form (using Auto Row Processing and Database Columns), and it pops up showing a list of employees (using the same SELECT statement as used when it was a SELECT LIST). So far, so good. When I select an employee, the employees name shows up in the field in grey text. So far, so good. However, I do not get anything helpful when I enter anything into the Search box. I just get a "The page cannot be displayed" in the window. Basically I am unable to search. Bummer.

However, when I look at the record that I just created, I do not see the employees name in the field anymore, I just see the employee's ID. I also get the following error message:

"Error: ORA-00904: "SESTAFFID": invalid identifier performing List of Values query: "select Employee, SEStaffID from ( SELECT ec.ename || ' - ' || s.site_name "Employee", wss.wss_id "SEStaffID" FROM wiser_se_staff wss, vw_emp_contact@WISER_SA ec, vw_site@WISER_SA s WHERE wss.staff_emp_id = ec.empid AND wss.staff_sch_location = s.site_id AND ((:P205_REF_RECD_BY_ID IS NOT NULL AND :P205_REF_RECD_BY_ID = wss.wss_id) OR :P205_REF_RECD_BY_ID IS NULL) ORDER BY ec.ename, s.site_name) wwvlovinlineviewname where SEStaffID = :WWV_LOV_RETURN_KEY_UTIL_1111". "

Dang, I hate it when that happens. I have not been able to find any references to this error that would shed light on how to handle this error.

I followed the recommendation in the HTML DB User's Guide for Popup LOVs (Table 6-7 Available Item Types), to no avail.

The SELECT statement that is being used is:

SELECT ec.ename || ' - ' || s.site_name "Employee",
wss.wss_id "SEStaffID"
FROM wiser_se_staff wss,
vw_emp_contact@WISER_SA ec,
vw_site@WISER_SA s
WHERE wss.staff_emp_id = ec.empid
AND wss.staff_sch_location = s.site_id
AND ((:P205_REF_RECD_BY_ID IS NOT NULL
AND :P205_REF_RECD_BY_ID = wss.wss_id)
OR :P205_REF_RECD_BY_ID IS NULL)
ORDER BY ec.ename, s.site_name

I guess my questions are:

1) Why is APEX choking on the SELECT statement for the UPDATE and not the CREATE?

2) Why doesn't the SEARCH feature work, or probably more correct, what am I missing?


TIA