SYMPTOM:
After upgrading Strategy 9.4.1 to Strategy 10.2. The following regression problem has been noticed:
Strategy 10.2 uses a different Oracle function to translate the Strategy MonthsBetween function into SQL in the Oracle 11g database.
When using MonthsBetween function in a fact, the SQL generated in Strategy 10.2 uses TRUNC instead of FLOOR Oracle function.
- SQL generated in Strategy 9.4.1:
- SQL generated in Strategy 10.2:
CAUSE:
This change reflects that after the calculation where the difference is negative, the result is incorrect.
Considering the following two dates: 28/02/2016 and 03/03/2016. The difference is one month, so the result of MonthsBetween function should be -1.
In version 9.4.1, which used FLOOR function to round the number the result was correct.
FLOOR(MONTHS_BETWEEN('28/02/2016', '03/03/2016'))
The result was -1.
In the version 10.2, which uses TRUNC function, the SQL looks like this:
TRUNC(MONTHS_BETWEEN('28/02/2016', '03/03/2016'))
The result is 0 which is incorrect.
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
