LOV Display Shows Return Value Instead of Display Value in APEX

The Problem

A select list or popup LOV shows “20” instead of “Marketing Department.” A display-only item shows the ID number instead of the descriptive name. The LOV query is correct, but the display value is not appearing.

Cause 1: Display Only Items Need LOV

When an item’s Type is “Display Only” or “Display Only (Save State)”, APEX shows the raw session state value which is the return value, not the display value. To show the display text, the item must have a List of Values defined even in display-only mode. Set the LOV on the item and ensure “Display Extra Values” is set to Yes in case the current value is not in the LOV query results.

Cause 2: LOV Query Returns Wrong Columns

APEX LOV queries must return the display value as the first column and the return value as the second column:

-- CORRECT: Display first, Return second
SELECT department_name AS d, department_id AS r
  FROM departments ORDER BY department_name;

-- WRONG: Return first, Display second (items show the ID)
SELECT department_id, department_name
  FROM departments ORDER BY department_name;

Cause 3: Null Handling

If the item’s value does not exist in the LOV query results, APEX shows the raw value. This happens when data has been deleted, when the LOV query has a WHERE clause that excludes the current value, or when the value was set programmatically to something not in the LOV. Enable “Display Extra Values” to show the raw value with an indicator, or adjust the LOV query to include all possible values.

Cause 4: Cascading LOV Not Loaded

For cascading LOVs, if the parent item has no value when the page loads, the child LOV query cannot execute (the bind variable is null), so the child item shows the raw return value. Ensure the parent item has a default value or handle the null case in the child LOV query.

Solution for Reports

In report columns, use a JOIN in the SQL query rather than relying on an LOV. This is more reliable and more performant than having APEX resolve LOV display values for every row:

SELECT o.order_id, d.department_name, o.total
  FROM orders o
  JOIN departments d ON o.department_id = d.department_id;