Oracle Business Intelligence

Calculating length of service by increment
Hi There,
I'm trying to calculate incremental anniversary dates using the formula below, but instead of showing 10 as the result, I really want the calculation to bring back the anniversary date i.e 10 + 25/12/2012 should bring back 25/12/2022.
CASE WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12 <10 AND (TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12)+2 >=10 THEN '10'
Can some one please help me with this
Answers
-
Look at the function TIMESTAMPADD and you will be able to get what you are after.
-
Thanks for your response, I have tried different TIMESTAMPADD syntaxes but always comes back with an error, I'm new to Oracle BI
-
An error ... like what?
What was your formula and what was your error?
-
Hi,
I use the syntax below
CASE WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12 <10 AND (TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12)+2 >=10 THEN TIMESTAMPADD(SQL_TSI_YEAR,10, "Employee Attributes"."Employee NHS Entry Date")
The error when it runs is:
-
Ok, the real error is behind the "[+] Error Details" link, if you expand it, you get the real error.
Your expression isn't a valid expression because your CASE WHEN is not closed.
The syntax of a CASE WHEN is:
CASE WHEN <condition> THEN <value/expression> [ WHEN <condition> THEN <value/expression> ]+ [ ELSE <value/expression> ] END
While additional WHEN-THEN or a ELSE are optional, your CASE WHEN must be closed by END.
-
Thanks Gianni, for your comments, it has now worked!
I was testing the first part of my case statement with a timestamp function and running not thinking this would affect the whole of the nested case statements as I have multiple anniversary dates to work on, Now I have a better understanding, thanks again!