Search code examples
sqlplsqloracle-sqldeveloper

relational operator error while using multiple condition in where clause

I have a date column and need to check couple of conditions for below scenario.. but looks like I am doing something wrong and thus getting error

scenario:
If EmpBonusDate column is null or
if (sysdate-EmpBonusDate >= some constant number) then return 1 else 0

-- I am multiplying output of (sysdate-EmpBonusDate) by 24 so, I can get a number which I can compare to check >= condition with constant number.

this is what my sql looks like

Select count(1) from employee emp where
Emp.IsActive = '1'
and (sysdate - emp.EmpBonusDate) * 24 >= 30 -- this works
-- or nvl(emp.EmpBonusDate,1) -- if I add or clause, I get [ORA-00920] invalid relational operator error....

any clue what I am doing wrong and how can I get the 1 or 0 for above mentioned scenario

Solution

  • With syntax or nvl(emp.EmpBonusDate,1) you're missing relationship, because you're not comparing that value with something.

    You can replace your where clause with this

    Emp.IsActive = '1' and ((sysdate - emp.EmpBonusDate) * 24 >= 30 or emp.EmpBonusDate is null)

    that's equivalent to your if clause.