This discussion is archived
8 Replies Latest reply: Jan 12, 2013 9:26 AM by VivekAnanda RSS

Oracle Maximum Date Query

NewApexCoder Newbie
Currently Being Moderated
i need assistance in creating a query

select a.job_number, a.title, a.request_date, a.completion_date, b.modification_number,b.actual_cost, b.shop_facility_percentage, d.status_date
from
survey_jobs a
left outer join survey_job_bundles b
on (a.survey_job_id = b.survey_job_id)
left outer join survey_job_status d on (b.survey_job_bundle_id = d.survey_job_bundle_id)
order by a.job_number asc, b.modification_number asc

this query produces a report that duplicates some of the rows. it does this because i am joining to a child to table that has a one to many relationship with its parent. The child table can have many dates (d.status_date). I want to pull the maximum date from this child table. I have created a max function that i was trying to plug in, but when i plug it in right under the last outer join, it reduces my report results.


here is my attempted max function

AND b.SURVEY_JOB_BUNDLE_ID = d.SURVEY_JOB_BUNDLE_ID
AND ((d.SURVEY_JOB_BUNDLE_ID IS NULL) OR
(d.SURVEY_JOB_BUNDLE_ID, d.STATUS_DATE) IN
(SELECT SURVEY_JOB_BUNDLE_ID, MAX(STATUS_DATE) FROM
SURVEY_JOB_STATUS GROUP BY SURVEY_JOB_BUNDLE_ID))


Can anyone help me with this? Thanks in advance

Edited by: 963510 on Jan 9, 2013 11:17 AM

Edited by: 963510 on Jan 9, 2013 11:22 AM

Edited by: 963510 on Jan 9, 2013 11:23 AM
  • 1. Re: Oracle Maximum Date Query
    damorgan Oracle ACE Director
    Currently Being Moderated
    GROUP BY the maximum date. You may want to then wrap that query in parentheses as an inline view and query it as an intermediate result set.

    BTW: Always post a version number, Always read the FAQ and learn how to post properly ... for example using
     tags, Always include DDL for the table and some sample data so people can try out a solution. We do not always have time to reinvent the wheel.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 2. Re: Oracle Maximum Date Query
    Kenny Hanberg Explorer
    Currently Being Moderated
    One solution could be..
    select a.job_number, 
           a.title, 
           a.request_date, 
           a.completion_date, 
           b.modification_number,
           b.actual_cost, 
           b.shop_facility_percentage, 
           ( select max(d.status_date) 
               from survey_job_status d 
              where b.survey_job_bundle_id = d.survey_job_bundle_id
           ) status_date
      from survey_jobs a
      left outer join survey_job_bundles b on (a.survey_job_id = b.survey_job_id)
     order by a.job_number asc, 
              b.modification_number asc
    /Kenny
  • 3. Re: Oracle Maximum Date Query
    NewApexCoder Newbie
    Currently Being Moderated
    Wow, that works perfectly. How did you come up with this so fast. And sorry about not changing the version numbers
  • 4. Re: Oracle Maximum Date Query
    NewApexCoder Newbie
    Currently Being Moderated
    I have also tried to add two more columns from the same "d" table. but everytime I try it says invalid identifier. Whats the problem??
  • 5. Re: Oracle Maximum Date Query
    NewApexCoder Newbie
    Currently Being Moderated
    Additional editing to query
  • 6. Re: Oracle Maximum Date Query
    Kenny Hanberg Explorer
    Currently Being Moderated
    I my last query Your table "d" moved out of the main query, and is calculated as each result row is passed back to the user.

    If You need additional info from the "d" table, the query needs some modification like this...
    select a.job_number, 
           a.title, 
           a.request_date, 
           a.completion_date, 
           b.modification_number,
           b.actual_cost, 
           b.shop_facility_percentage, 
           --d.status_date
           d.*
      from survey_jobs a
      left outer join survey_job_bundles b on (a.survey_job_id = b.survey_job_id)
      left outer join survey_job_status  d on (b.survey_job_bundle_id = d.survey_job_bundle_id
                                               and d.status_date
                                                 = (select max(d.status_date)
                                                      from survey_job_status d2 
                                                   where d2.survey_job_bundle_id = d.survey_job_bundle_id
                                                   )
                                              )
     order by a.job_number asc, 
              b.modification_number asc
    /Kenny
  • 7. Re: Oracle Maximum Date Query
    NewApexCoder Newbie
    Currently Being Moderated
    I had tried using your posted example, however, I get the ORA-01799 error stating a column may not be outer joined to a subquery. I will research to see if I can fix this problem.
  • 8. Re: Oracle Maximum Date Query
    VivekAnanda Newbie
    Currently Being Moderated
    try
    select a.job_number, 
           a.title, 
           a.request_date, 
           a.completion_date, 
           h.modification_number,
           h.actual_cost, 
           h.shop_facility_percentage, 
           h.status_date
      from survey_jobs a
      left outer join (
      select b.survey_job_id,b.modification_number,b.actual_cost,b.shop_facility_percentage, d.status_date
      from survey_job_bundles b
      left outer join survey_job_status  d on (b.survey_job_bundle_id = d.survey_job_bundle_id
                                               and d.status_date
                                                 = (select max(d.status_date)
                                                      from survey_job_status d2 
                                                   where d2.survey_job_bundle_id = d.survey_job_bundle_id
                                                   )
                                              ) h on (a.survey_job_id = h.survey_job_id)
     order by a.job_number asc, 
              b.modification_number asc
                

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points