When creating a 'select list' item, the List of Values section allows you to specify the display and return value for null (no selection). If left empty, the null value is '%null%', which appears to confuse other HTML DB pages. For example, if I set an item's 'Default Value' property to '%null%', apply changes and then edit the item, the '%null%' disappears!?!? (I think this is an HTML DB BUG)
I am trying to use the select list as a 'quick find' on a report. I display the null value as '(all)' in the select list and then use the item value in the where clause as follows:
... WHERE (:P10_TIMEZONE_QUICK_FIND IS NULL OR TIMEZONE = :P10_TIMEZONE_QUICK_FIND)
in other words, WHERE (the select list is '(all)' or the field = the value in the select list).
However, since not selecting a value in the select list causes the item to be set to '%null%', I have to write the query like this:
... WHERE (:P10_TIMEZONE_QUICK_FIND = '%null%' OR TIMEZONE = :P10_TIMEZONE_QUICK_FIND)
But this doesn't work the first time through since the item is actually null, so I have to account for both possibiities in my query:
... WHERE (:P10_TIMEZONE_QUICK_FIND IS NULL OR :P10_TIMEZONE_QUICK_FIND ='%null%' OR TIMEZONE = :P10_TIMEZONE_QUICK_FIND)
which seems like a rather ugly workaround.
- Is this an HTML DB bug?
- Is there a better way to handle this?