Search code examples
oracle-databasewhere-clausenvl

NVL error in query Oracle

After updating the oracle the query below starts returning strange results, it should returns column with some values but returns null, after some twiching with the query, found that if I change t.event_id = d.event_id with t.event_id = 125162 query start working like before. Any advice?

SELECT LEAST (
          MAX (
             d.event_step
             - (CASE WHEN d.status IN (51, 52, 53) THEN 0 ELSE 2 END)),
          3)
          AS nextStep
  FROM x_events_step d
 WHERE d.event_id = 125162 AND d.state = 1
       AND d.event_step_id >
              NVL ( (SELECT MAX (t.event_step_id)
                       FROM x_events_step t
                      WHERE t.event_id = d.event_id AND t.state = 2),
                   0);
Solution

  • After upgrade from Oracle 11.2.0.2 to 11.2.0.3 the problem was resolved.