Nested Case statement in Oracle BI

5 pts.
Tags:
CASE statement
Oracle BI
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.

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.

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

Following

Share this item with your network: