This morning, I was building a series of three LOVs. The first two display categories (parents and their children), and the third is supposed to display people associated with those categories. The parent (LOV #1) is required, the child (LOV #2) is optional. Simple issue, which is quickly dealt with by APEX’s Cascading LOV feature, right?
Well, it should have been. But when I ran the page, I found that the people (LOV #3) weren’t populated unless both of the other LOVs had values. If I selected the parent but not the child, no people were available to select from, even though the people’s SQL correctly used NVL.
Luckily, I noticed the “Optimize Refresh” attribute of the third LOV, which had defaulted to Yes. I didn’t remember what it actually did, but the help for it confirmed that if it’s set to Yes, APEX assumes that the list will be null if any of the parent values are null, and so doesn’t even bother running the query. Setting it to No resolved the issue.