# Case Statement with Calculations in Oracle 11g

Hi Guys,

I have a query where I'm trying to use a case statement.
In this case statement, I'm trying to determine the years of service (YOS).
The calculation for that is: round((sysdate - tae.accr_begin_dt)/365).
Problem is that the only way I can figure out to do it is to repeat the same formula over and over in my case statement.
Ideally, I would like for this to be stored as variable, so that I can reference it later.
I know there has to be a smarter way to write this code:

SELECT emp.employee,
emp.last_name,
emp.first_name,
emp.middle_init,
emp.city,
emp.state,
emp.zip,
emp.emp_status,
emp.department,
emp.job_code,
emp.pay_rate,
emp.fte_total,
emp.pro_rate_a_sal,
jbc.description,
zzw.userid,
tae.employee_group,
tae.accr_begin_dt,
tae.resrv_hrs_bal,
(tae.resrv_hrs_bal/8) AS DT_DAYS,
(tae.resrv_hrs_bal * emp.pay_rate) as DT_VALUE,
(case
when tae.employee_group = 'LPZ-SALARY' and round((sysdate - tae.accr_begin_dt)/365) < 1 then (8.92 * 26 * emp.fte_total)
when tae.employee_group = 'LPZ-SALARY' and round((sysdate - tae.accr_begin_dt)/365) = 1 then (9.23 * 26 * emp.fte_total)
when tae.employee_group = 'LPZ-SALARY' and round((sysdate - tae.accr_begin_dt)/365) = 2 then (9.54 * 26 * emp.fte_total)
when tae.employee_group = 'LPZ-SALARY' and round((sysdate - tae.accr_begin_dt)/365) = 3 then (9.85 * 26 * emp.fte_total)
when tae.employee_group = 'LPZ-SALARY' and round((sysdate - tae.accr_begin_dt)/365) = 4 then (10.15 * 26 * emp.fte_total)
when tae.employee_group = 'LPZ-SALARY' and round((sysdate - tae.accr_begin_dt)/365) >= 5 then (10.46 * 26 * emp.fte_total)
else null
end ) as PTO

FROM PROD.employee emp,
PROD.jobcode jbc,
PROD.zzwebuser zzw,
PROD.taeemaster tae

WHERE emp.company = jbc.company
and   emp.job_code = jbc.job_code
and   emp.company = zzw.company
and   emp.employee = zzw.employee
and   emp.company = tae.company
and   emp.employee = tae.employee
and   emp.department not in ('95001','95130','99900')
and   emp.emp_status not in ('AT', 'PN','SR','T')
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
put your query except for the case in an inline view with that formula aliased to some useful name.

then select from the inline view using that alias as your "variable"
Commented:
I would use months_between(sysdate,tae.accr_begin_dt)/12 for years not divide by 365.

Use an inline view.  then reference the columns in the outer query.

Example:
select case when employee_group = 'LPZ-SALARY' and years_of_service < 1 then ...
from
(
select round((sysdate - tae.accr_begin_dt)/365) years_of_service, ... from ...
--the rest of your entire select
)
Commented:
that might look something like this...

SELECT employee,
last_name,
first_name,
middle_init,
city,
state,
zip,
emp_status,
department,
job_code,
pay_rate,
fte_total,
pro_rate_a_sal,
description,
userid,
employee_group,
accr_begin_dt,
resrv_hrs_bal,
dt_days,
dt_value,
(CASE
WHEN tae.employee_group = 'LPZ-SALARY' AND your_calculated_value < 1
THEN
(8.92 * 26 * emp.fte_total)
WHEN tae.employee_group = 'LPZ-SALARY' AND your_calculated_value = 1
THEN
(9.23 * 26 * emp.fte_total)
WHEN tae.employee_group = 'LPZ-SALARY' AND your_calculated_value = 2
THEN
(9.54 * 26 * emp.fte_total)
WHEN tae.employee_group = 'LPZ-SALARY' AND your_calculated_value = 3
THEN
(9.85 * 26 * emp.fte_total)
WHEN tae.employee_group = 'LPZ-SALARY' AND your_calculated_value = 4
THEN
(10.15 * 26 * emp.fte_total)
WHEN tae.employee_group = 'LPZ-SALARY'
AND ROUND((SYSDATE - tae.accr_begin_dt) / 365) >= 5
THEN
(10.46 * 26 * emp.fte_total)
ELSE
NULL
END)
AS pto
FROM (SELECT emp.employee,
emp.last_name,
emp.first_name,
emp.middle_init,
emp.city,
emp.state,
emp.zip,
emp.emp_status,
emp.department,
emp.job_code,
emp.pay_rate,
emp.fte_total,
emp.pro_rate_a_sal,
jbc.description,
zzw.userid,
tae.employee_group,
tae.accr_begin_dt,
tae.resrv_hrs_bal,
(tae.resrv_hrs_bal / 8) AS dt_days,
(tae.resrv_hrs_bal * emp.pay_rate) AS dt_value,
ROUND((SYSDATE - tae.accr_begin_dt) / 365) your_calculated_value
FROM prod.employee emp,
prod.jobcode jbc,
prod.zzwebuser zzw,
prod.taeemaster tae
WHERE emp.company = jbc.company
AND emp.job_code = jbc.job_code
AND emp.company = zzw.company
AND emp.employee = zzw.employee
AND emp.company = tae.company
AND emp.employee = tae.employee
AND emp.department NOT IN ('95001', '95130', '99900')
AND emp.emp_status NOT IN ('AT',
'PN',
'SR',
'T'))
Senior Oracle DBACommented:
Move the calculation to an inline view like this:
``````SELECT emp.employee,
emp.last_name,
emp.first_name,
emp.middle_init,
emp.city,
emp.state,
emp.zip,
emp.emp_status,
emp.department,
emp.job_code,
emp.pay_rate,
emp.fte_total,
emp.pro_rate_a_sal,
jbc.description,
zzw.userid,
tae.employee_group,
tae.accr_begin_dt,
tae.resrv_hrs_bal,
(tae.resrv_hrs_bal /8)             AS dt_days,
(tae.resrv_hrs_bal * emp.pay_rate) AS dt_value, (
CASE
WHEN tae.yos < 1 THEN (8.92   * 26 * emp.fte_total)
WHEN tae.yos = 1 THEN (9.23   * 26 * emp.fte_total)
WHEN tae.yos = 2 THEN (9.54   * 26 * emp.fte_total)
WHEN tae.yos = 3 THEN (9.85   * 26 * emp.fte_total)
WHEN tae.yos = 4 THEN (10.15  * 26 * emp.fte_total)
WHEN tae.yos >= 5 THEN (10.46 * 26 * emp.fte_total)
ELSE NULL
END ) AS pto
FROM   prod.employee emp,
prod.jobcode jbc,
prod.zzwebuser zzw,
(
SELECT prod.taeemaster.*,
CASE
WHEN tae.employee_group = 'LPZ-SALARY' THEN Round((SYSDATE - tae.accr_begin_dt)/365)
ELSE NULL yos
FROM   prod.taeemaster) tae
WHERE  emp.company = jbc.company
AND    emp.job_code = jbc.job_code
AND    emp.company = zzw.company
AND    emp.employee = zzw.employee
AND    emp.company = tae.company
AND    emp.employee = tae.employee
AND    emp.department NOT IN ('95001',
'95130',
'99900')
AND    emp.emp_status NOT IN ('AT',
'PN',
'SR',
'T')
``````
You should modify the inline view query so that it only selects the columns that are required from the sub table.  The optimizer will probably do it, but it is always better if you do it.
Author Commented:
@johnsone...I tried your solution, but get an error stating I'm missing a keyword.
developer states the error is here: ELSE NULL yos
Senior Oracle DBACommented:
Sorry.  Missed the end on the case statement in the subquery.
``````SELECT emp.employee,
emp.last_name,
emp.first_name,
emp.middle_init,
emp.city,
emp.state,
emp.zip,
emp.emp_status,
emp.department,
emp.job_code,
emp.pay_rate,
emp.fte_total,
emp.pro_rate_a_sal,
jbc.description,
zzw.userid,
tae.employee_group,
tae.accr_begin_dt,
tae.resrv_hrs_bal,
( tae.resrv_hrs_bal / 8 )            AS dt_days,
( tae.resrv_hrs_bal * emp.pay_rate ) AS dt_value,
( CASE
WHEN tae.yos < 1 THEN ( 8.92 * 26 * emp.fte_total )
WHEN tae.yos = 1 THEN ( 9.23 * 26 * emp.fte_total )
WHEN tae.yos = 2 THEN ( 9.54 * 26 * emp.fte_total )
WHEN tae.yos = 3 THEN ( 9.85 * 26 * emp.fte_total )
WHEN tae.yos = 4 THEN ( 10.15 * 26 * emp.fte_total )
WHEN tae.yos >= 5 THEN ( 10.46 * 26 * emp.fte_total )
ELSE NULL
END )                              AS pto
FROM   prod.employee emp,
prod.jobcode jbc,
prod.zzwebuser zzw,
(SELECT prod.taeemaster.*,
CASE
WHEN tae.employee_group = 'LPZ-SALARY' THEN Round((
SYSDATE - tae.accr_begin_dt ) / 365)
ELSE NULL
END yos
FROM   prod.taeemaster) tae
WHERE  emp.company = jbc.company
AND emp.job_code = jbc.job_code
AND emp.company = zzw.company
AND emp.employee = zzw.employee
AND emp.company = tae.company
AND emp.employee = tae.employee
AND emp.department NOT IN ( '95001', '95130', '99900' )
AND emp.emp_status NOT IN ( 'AT', 'PN', 'SR', 'T' )
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
isn't an inline view essentially the same thing I posted earlier?
Senior Oracle DBACommented:
The view I did is slightly different.  More of the calculation is in the inline view and the inline view only has one table.

And I was posting mine at the same time you posted yours.
Author Commented:
This worked perfectly.
Thank you
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.