Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle query on a dataware house table-- challenging query

Posted on 2016-08-26
5
Medium Priority
?
71 Views
Last Modified: 2016-08-27
Hi Team ,

Below is the query that i have written to fetch the latest record for a empid , based on the effective_date and effective_seq. the requirement is an employee may have many changes happening in regard to his salary , position etc. this recorded by the means of effective date , effective sequence , status.

below is the table structure and data:
Job table data:

EMPID               EMPREC EFFECTIVE EFFECTIVE_SEQ S ACTION
--------------- ---------- --------- ------------- - --------------------
001                      0 01-JAN-16             0 A Hire
001                      0 31-MAR-16             0 A Pay-Change
001                      0 31-MAR-16             1 A Line-manager-Change
001                      0 01-APR-16             0 I Termination
002                      0 31-MAR-16             2 I Terminated
002                      0 31-MAR-16             0 A Hire
002                      0 31-MAR-16             1 A Pay-Change
003                      0 31-MAR-16             1 A Pay-change
003                      0 31-MAR-16             0 A Hire
004                      0 29-MAR-16             0 A Hire
004                      0 30-MAR-16             0 A Pay-Change

note the effective seq column is incremented by for each change on the same day.

for a given date say 31-mar-2016 , i must pull out all the latest record for the empoyees.

expected out put is:

003                      0 31-MAR-16             1 A Pay-change
001                      0 31-MAR-16             1 A Line-manager-Change
004                      0 30-MAR-16             0 A Pay-Change


query written is :

Select *
from
(
Select Empid , Effective_date , Effective_seq, Status, Action ,
row_number() over (partition by Empid order by Effective_seq desc  , effective_date desc) rk
from job)
where rk=1  and status ='A' and Effective_date <=to_date('31-MAR-2016','DD-MON-YYYY');


EMPID           EFFECTIVE EFFECTIVE_SEQ S ACTION                       RK
--------------- --------- ------------- - -------------------- ----------
001             31-MAR-16             1 A Line-manager-Change           1
003             31-MAR-16             1 A Pay-change                    1
004             30-MAR-16             0 A Pay-change                    1


I want to know whether the approach i have followed is correct , is there a better way of writing the above query , Really appreciate for any feedback.
0
Comment
Question by:sam_2012
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41772025
that is probably the correct approach.

However, if you have an index on Empid,Effective_seq, effective_date
or if you are using 12c and have these columns in a in-memory column stores, then it's possble that using a subquery to find the max rows might be more efficient and then querying the table again to retrieve the rest of the table columns.

you'll have to test both ways to determine which is better
0
 
LVL 32

Expert Comment

by:awking00
ID: 41772048
An observation note (no points, please) - If effective_date contains a time element then Effective_date <=to_date('31-MAR-2016','DD-MON-YYYY') would not include 31-MAR-2016 (except for midnight).  You may want to consider using < to_date('01-APR-2016','DD-MON-YYYY') to be safe.
0
 

Author Comment

by:sam_2012
ID: 41772544
Hi sdStuber,

Below is the query using subquery , can help me whether this query is fine

Select *
from job a
where effective_seq =Any (Select max(effective_seq) from job b where
                                             b.empid=a.empid
                          )
and effective_date =Any(Select max(effective_date) from job c
                                          where c.empid=a.empid and c.status=a.status)
and a.status='A'   and a.effective_date <=to_date('31-MAR-2016','DD-MON-YYYY')
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 41772551
no the functional equivalent to the analytic version would be something like this...


SELECT *
  FROM job a
 WHERE (effective_seq, effective_date) IN
           (SELECT MAX(effective_seq), MAX(effective_date) KEEP (DENSE_RANK FIRST ORDER BY effective_seq DESC)
              FROM job b
             WHERE b.empid = a.empid)
   AND a.status = 'A'
   AND a.effective_date <= TO_DATE('31-MAR-2016', 'DD-MON-YYYY');
0
 

Author Closing Comment

by:sam_2012
ID: 41773127
awesome
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question