latest version of apex on the cloud , and firefox 24 ,
i have a report and there are some items above it to search with it , these items are of type select lists , but the problem is
when i write
select ........ from ..........
where staff_name = nvl(:p29_staff_name,staff_name) , and run the page successfully and try to search with the staff name list , i get no data found ,
although there are data with this staff name ??
this thing happens only with select lists , i have other text fields and the search is doing well with them .
What is the LOV you are using the for the Select List?
The select lists uses a display value and a separate return value - which would be used in the where clause of your query.
So if your select list was
select staff_name, staff_id from my_staff
then you would be comparing ID to staff name, hence no results.
Have you considered Scott Wesley's answer? Make sure both the display and return value of your select list LOV query is the STAFF_NAME column.
That's perfectly fine in this situation. You need the name of the staff as the return value of your select list. That's why both the return and display column are identical.
Ideally your search page's SQL would use
select ........ from .......... where staff_id = nvl(:p29_staff_id,staff_name)
and your LOV would be as I previously mentioned.
This is because it's likely there would/should be an indext on staff_id - but we don't know your structure.
The only difference between the NVL and DECODE in this case is readability.
Really you should forget both of these and get in the habit of using COALESCE and CASE.