I have inherited some SQL from another developer which needs to be updated and I am drawing a blank here.. Currently the select has multiple conditions in the where clause and returns data even if all the conditions are false. What we need is to return rows ONLY if atleast one of the conditions is true..
Modified version of query is (Removed sensative information and such):
select
h.name,
h.fiscal_year,
h.period,
h.category_id,
h.Due_date,
from HEADER h
WHERE h.category_id = NVL2(:P30_CATEGORY,:P30_CATEGORY,h.category_id) AND
h.name = NVL2(:P30_NAME,:P30_NAME,h.name) AND
h.fiscal_year = NVL2(:P30_FISCAL_YEAR,:P30_FISCAL_YEAR,h.fiscal_year) AND
h.period = NVL2(:P30_PERIOD,:P30_PERIOD,h.Period) AND
h.Due_date = NVL2(:P30_DATE,TO_DATE(:P30_DATE,'DD-MON-YYYY'),h.due_Date)
order by h.name desc;
So if the P30_CATEGORY is NULL and all the rest of the bind variables are null, we expect no data back.. However at this time if all binds ar enull, we still ar egetting rows returned..
Any help appreciated!
Thank you,
Tony MIller
Los Alamos, NM