Where clause problem in Oracle 8i - Can you "Where case when then..."

To begin with the CASE expression - the key point is that CASE is an expression, not a statement - it has to return a value. Your CASE looks like this:

Code:

where 
case
  When d = 2 then o.adddte between date1 and date2
  when d = 3 then o.adddte between date3 and date4
  when d IN (1, 4, 6, 5, 7) then o.adddte between date5 and date6
end

This is incorrect use of a CASE. In fact, you could be using OR:

Code:

where 
  (  ( d = 2 and o.adddte between date1 and date2 )
  or (d = 3 and o.adddte between date3 and date4)
  or (d IN (1, 4, 6, 5, 7) and o.adddte between date5 and date6)
  )

To use CASE, your WHERE clause would have to be:

Code:

where o.adddte between
  case
    When d = 2 then date1
    when d = 3 then date3
    else date5
  end
and 
  case
    When d = 2 then date2
    when d = 3 then date4
    else date6
  end

The other problem is the invalid expressions in your SQL like this one:
to_date ('trunc(sysdate-4) 15:00', 'dd/mm/yyyy hh24:mi')
This should be:
to_date(to_char(trunc(sysdate-4),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
or more simply:
trunc(sysdate-4)+15/24