Nested Case statement in Oracle BI

5 pts.
Tags:
CASE statement
Oracle BI (business intelligence)
Oracle SQL
I am trying to create a nested case statement in Oracle BI (9.0.4) the statement works when I do it in Oracle SQL*Plus but will not let me keep the whole script in the Calculation.What do I need to doo to have this work in Oracle BI? The statement is as follows: select emp_name, (CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 5 THEN '5 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - 1825) * -1), 99999) END) TOTAL_DAYS_UNTIL_5_YRS_SERV ,(CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 10 THEN '10 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - (1825 * 2)) * -1), 999999) END) TOTAL_DAYS_UNTIL_10_YRS_SERV ,(CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 15 THEN '15 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - (1825 * 3)) * -1), 999999) END) TOTAL_DAYS_UNTIL_15_YRS_SERV ,(CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 20 THEN '20 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - (1825 * 4)) * -1), 999999) END) TOTAL_DAYS_UNTIL_20_YRS_SERV ,(CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 25 THEN '25 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - (1825 * 5)) * -1), 999999) END) TOTAL_DAYS_UNTIL_25_YRS_SERV ,(CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 30 THEN '30 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - (1825 * 6)) * -1), 999999) END) TOTAL_DAYS_UNTIL_30_YRS_SERV ,(CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 35 THEN '35 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - (1825 * 7)) * -1), 999999) END) TOTAL_DAYS_UNTIL_35_YRS_SERV ,(CASE WHEN TO_CHAR(((SYSDATE - EMP_HIRE_DATE) / 365),99.99) >= 40 THEN '40 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - EMP_HIRE_DATE) - (1825 * 8)) * -1), 999999) END) TOTAL_DAYS_UNTIL_40_YRS_SERV from emp_employee order by emp_name

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think you could achieve the same result by using the decode and sign functions, and this could shorten your query a little.

Try this:

<pre>Select emp_name,
decode(sign(emp_hire_date+1825-sysdate),-1,’5 Years of Service Obtained’,to_char(emp_hire_date-sysdate+1825,999999)) TOTAL_DAYS_UNTIL_5_YRS_SERV,
decode(sign(emp_hire_date+3650-sysdate),-1,’10 Years of Service Obtained’,to_char(emp_hire_date-sysdate+3650,999999)) TOTAL_DAYS_UNTIL_10_YRS_SERV,
decode(sign(emp_hire_date+5475-sysdate),-1,’15 Years of Service Obtained’,to_char(emp_hire_date-sysdate+5475,999999)) TOTAL_DAYS_UNTIL_15_YRS_SERV,
decode(sign(emp_hire_date+7300-sysdate),-1,’20 Years of Service Obtained’,to_char(emp_hire_date-sysdate+7300,999999)) TOTAL_DAYS_UNTIL_20_YRS_SERV,
decode(sign(emp_hire_date+9125-sysdate),-1,’25 Years of Service Obtained’,to_char(emp_hire_date-sysdate+9125,999999)) TOTAL_DAYS_UNTIL_25_YRS_SERV,
decode(sign(emp_hire_date+10950-sysdate),-1,’30 Years of Service Obtained’,to_char(emp_hire_date-sysdate+10950,999999)) TOTAL_DAYS_UNTIL_30_YRS_SERV,
decode(sign(emp_hire_date+12775-sysdate),-1,’35 Years of Service Obtained’,to_char(emp_hire_date-sysdate+12775,999999)) TOTAL_DAYS_UNTIL_35_YRS_SERV,
decode(sign(emp_hire_date+14600-sysdate),-1,’40 Years of Service Obtained’,to_char(emp_hire_date-sysdate+14600,999999)) TOTAL_DAYS_UNTIL_40_YRS_SERV
from emp_employee
order by emp_name;</pre>

You might also consider using shorter aliases for the columns.

regards,

-Carlosdl

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following