I always understood that the CASE
statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition? is related but doesn't appear to cover this situation and relates to SQL Server).
In the following example, I wish to calculate the MAX(amount)
between a range of months that differs based on how many months are between the start and paid dates.
(This is obviously a constructed example but the logic has valid business reasoning in the actual code where I see the issue).
If there are < 5 months between the start and paid dates then Expression 1 will be used otherwise Expression 2 will be used.
This results in the error "ORA-01428: argument '-1' is out of range" because 1 record has an invalid data condition that results in a negative value for the start of the BETWEEN clause of the ORDER BY.
Query 1
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
So I went for this second query to first eliminate anywhere this can occur:
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
Unfortunately, there is some unexpected behaviour that means that the values Expression 1 WOULD use are validated, even though the statement will not be executed because the negative condition is now trapped by the outer CASE
.
I can get around the issue by using ABS
on the MONTHS_BETWEEN
in Expression 1, but I feel like this should be unnecessary.
Is this behaviour as expected ? If so 'why' as it seems illogical to me and more like a bug ?
This will create a table and test data. The query is simply me checking that the correct path in the CASE
is being taken.
CREATE TABLE payment
(ref_no NUMBER,
start_date DATE,
paid_date DATE,
amount NUMBER)
INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('01-01-2016','DD-MM-YYYY'),3000)
INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('12-12-2015','DD-MM-YYYY'),5000)
INSERT INTO payment
VALUES (1001,TO_DATE('10-03-2016','DD-MM-YYYY'),TO_DATE('10-02-2016','DD-MM-YYYY'),2000)
INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('03-03-2016','DD-MM-YYYY'),6000)
INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('28-11-2015','DD-MM-YYYY'),10000)
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
'<5'
-- 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
'>=5'
-- MAX(amount)
-- OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
-- BETWEEN 5 PRECEDING AND CURRENT ROW)
END
END
FROM payment
MAX(amount) OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS BETWEEN GREATEST(0, LEAST(5, MONTHS_BETWEEN(paid_date, start_date))) PRECEDING AND CURRENT ROW)