Solved

oracle query on a dataware house table-- challenging query

Posted on 2016-08-26
5
61 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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Display based on value 4 39
Can't Access My Database 57 77
DECLARATION OF CURSOR IS INCOMPLETE OR MALFORMED 5 24
Oracle Nested table uses ? 2 35
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

830 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