Unexpected CASE evaluation logic

So it was hard for me to determine what your actual question was from the post, but I assume it is that when you execute:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
   ELSE
      CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC 
            ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING 
            AND CURRENT ROW)
      ELSE
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC 
            ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
      END                
   END
FROM payment

You still get ORA-01428: argument '-1' is out of range?

I don't think this is a bug. I think it is an order of operation thing. Oracle needs to do the analytics on all of the rows returned by the resultset. Then it can get down to the nitty gritty of transforming the output.

A couple of additional ways around this would be to exclude the row with a where clause:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
   -- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC 
         ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
         AND CURRENT ROW)
   ELSE
   -- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC 
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END                
END 
FROM payment
-- this excludes the row from being processed
where MONTHS_BETWEEN(paid_date, start_date) > 0 

Or you could embed a case in your analytic like:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC 
               ROWS BETWEEN 
               -- This case will be evaluated when the analytic is evaluated
               CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 
                THEN 0 
                ELSE MONTHS_BETWEEN(paid_date, start_date) 
                END 
              PRECEDING
              AND CURRENT ROW)
   ELSE
-- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC 
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END                
END 
FROM payment

Explanation

I wish I could find some documentation to back up the order of operation thing, but I haven't been able to find anything...yet.

The CASE short-circuit evaluation happens after the analytic function is evaluated. The order of operations for the query in question would be:

1. from payment
2. max over()
3. case.

So since the max over() happens before the case, the query fails.

Oracle's analytic functions would be considered a row source. If you execute an explain plan on your query, you should see a "window sort" which is the analytic, generating rows, which are fed to it by the previous row source, the payment table. A case statement is an expression that is evaluated for each row in the row source. So it makes sense (to me at least), that the case happens after the analytic.