×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PL/SQL - selecting the MAX() of a date field with criteria from another table

PL/SQL - selecting the MAX() of a date field with criteria from another table

PL/SQL - selecting the MAX() of a date field with criteria from another table

(OP)
So I have 2 tables:
PERSON -> is a table of basic info regarding members in the company (unique key here is PERSON_ID)
sample records:
PERSON_ID DEPT
123 abc
111 abc
100 bdc
200 cda
140 abc

EARN_HIST -> is a table of historical earnings for each member (unique key here is PERSON_ID, EARN_DATE) and has a 1-to-many relationship to PERSON table
sample records:
PERSON_ID EARN_DATE EARNINGS
123 2002/1/1 $1000
123 2010/1/1 $2000
100 2009/1/1 $15000
100 2011/1/1 $10000
111 2008/1/1 $5000
111 2008/7/1 $7000
111 2012/1/1 $10000
140 2010/6/1 $5000
140 2010/12/1 $7500
etc, etc, etc

My goal is to retrieve the most current Earning record for each member where DEPT = 'abc'
so my expected result is:
PERSON_ID EARN_DATE EARNINGS
123 2010/1/1 $2000
111 2008/7/1 $7000
140 2010/12/1 $7500

can this be done be just a query? or do I have to define a Store Procedure and/or Function to achieve my results?
any help on this would be greatly appreciated smile

RE: PL/SQL - selecting the MAX() of a date field with criteria from another table

(OP)
Well... i was successful in creating a function that would return the most current Earning record when passing in a PERSON_ID

CODE -->

CREATE OR REPLACE FUNCTION GET_CURRENT_EARN(inMbr in person.person_id%type)
  RETURN NUMBER IS

  v_Event number(5);

begin
  select a.EARNINGS
    into v_Event
    from EARN_HIST a
   where a.EARN_DATE = (select max(d.earn_date)
                           from EARN_HIST d
                          where a.PERSON_ID = d.PERSON_ID
                          group by d.PERSON_ID)
     and a.PERSON_ID = inMbr

  return v_Event;

end GET_CURRENT_EARN; 

what I wasn't successful in was when I try to call this function with this SQL statement

CODE -->

SELECT person_id, GET_CURRENT_EARN(person_id) FROM PERSON WHERE DEPT = 'abc'; 

I get the following error:
ORA-01422: exact fetch returns more than requested number of rows

so what do I need to do with this function to return the latest earnings amount for all the members that I'm querying based on:

CODE -->

SELECT person_id FROM PERSON WHERE DEPT = 'abc'; 
or is there any other method besides using a function?

RE: PL/SQL - selecting the MAX() of a date field with criteria from another table

I would start by getting rid of the GROUP BY in your subquery. Since you are only pulling the MAX in your select list, you do not need to group anything. Also, what is the primary key on your history table? It may be that you have a duplicate person_id/earn_date situation in your table.

RE: PL/SQL - selecting the MAX() of a date field with criteria from another table

(OP)
thx carp,

you were right about the duplicates and the group by
so i just adjusted the function to return the TOP 1 record and its working now

CODE -->

CREATE OR REPLACE FUNCTION GET_CURRENT_EARN(inMbr in person.person_id%type)
  RETURN NUMBER IS

  v_Event number(5);

begin
  select *
    into v_Event
  from (select a.EARNINGS
    into v_Event
    from EARN_HIST a
   where a.EARN_DATE = (select max(d.earn_date)
                           from EARN_HIST d
                          where a.PERSON_ID = d.PERSON_ID
                          group by d.PERSON_ID)
     and a.PERSON_ID = inMbr)
  where rownum = 1;

  return v_Event;

end GET_CURRENT_EARN; 

thanks again smile

RE: PL/SQL - selecting the MAX() of a date field with criteria from another table

and going back to the other point of your question.

try

CODE

SELECT person_id
      ,a.EARNINGS
from PERSON b
INNER JOIN EARN_HIST a
on a.person_id = b.person_id
and a.EARN_DATE = (select max(d.earn_date)
                     from EARN_HIST d
                    where a.PERSON_ID = d.PERSON_ID
                    group by d.PERSON_ID) 

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: PL/SQL - selecting the MAX() of a date field with criteria from another table

And just to avoid future issues, if there are not supposed to be duplicate records in the table I would see what I could do about getting rid of them. And while you're at it, try to figure out how they are getting in there to begin with and eliminate the source.

As for a query, you could also try:

CODE

SELECT p.person_id, e.earnings
  FROM person p
       INNER JOIN earn_hist e
          ON p.person_id = e.person_id
             AND p.dept = 'abc'
       INNER JOIN (SELECT person_id, max(earn_date)
                     FROM earn_hist 
                    GROUP BY person_id) v
          ON e.person_id = v.person_id
             AND e.earn_date = v.earn_date; 
Disclaimer - I haven't run this query; just cobbled it up on the fly. But I think it should work.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close