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.addr1,
             emp.addr2,
             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')
metalteckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

sdstuberCommented:
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"
slightwv (䄆 Netminder) 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
)
sdstuberCommented:
that might look something like this...


SELECT employee,
       last_name,
       first_name,
       middle_init,
       addr1,
       addr2,
       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.addr1,
               emp.addr2,
               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'))
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
Move the calculation to an inline view like this:
SELECT emp.employee, 
       emp.last_name, 
       emp.first_name, 
       emp.middle_init, 
       emp.addr1, 
       emp.addr2, 
       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')

Open in new window

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.
metalteckAuthor 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
johnsoneSenior 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.addr1, 
       emp.addr2, 
       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' ) 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
sdstuberCommented:
isn't an inline view essentially the same thing I posted earlier?
johnsoneSenior 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.
metalteckAuthor 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.