Solved

Oracle - max date filter issue

Posted on 2012-12-28
4
589 Views
Last Modified: 2013-01-10
Hello all

I have this query bellow that pulls the Max date.

But when i query, i dont get the max date.

  SELECT   DISTINCT A.CARRIER_ID,
                    MAX (A.EFFECTIVE),
                    A.TRLR_TYPE_ID,
                    A.CURRENT_NUM_VEHICLES,
                    L.NAME
    FROM   TL_RESOURCE_TRLR A, CARRIER_EQUIP B,LANE L
   WHERE   A.CARRIER_ID = B.CARRIER_ID(+)
           AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
           AND A.LANE_ID = L.ID
           AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE  OF THE OUTER JOIN
GROUP BY   a.carrier_id, a.trlr_type_id, current_num_vehicles, L.NAME
ORDER BY   A.CARRIER_ID ASC;

Open in new window


Example:

From resources table:
date in date

From Query result:
Date in query result
How can i fix that please?

Thanks again for your help
0
Comment
Question by:Wilder1626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 38727293
>>AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE  OF THE OUTER JOIN<<
I'm not sure what your comment means in that you're trying to join on A.CARRIER_ID = B.CARRIER_ID. Could you provide some sample relevant data for the two tables and what you expect as results?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38727480
Oh sure.

Here you will see the resources extract and also the resources query result.

Thanks again
resources-TABLE.xls
resources-result.xls
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 250 total points
ID: 38730237
This query looks very strange to me.  You have a combination of "distinct" plus "max" on a.effective.  Are you sure that that will give you the result you want?  Then, you have an outer join, plus a "where" clause that restricts the rows to only those that are outer joined, but you have a comment that indicates you want the outer-joined rows to be excluded.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38765461
Hello guys,

Thanks for your help and sorry for the delay. I will work this out a little bit more.

markgeerm you are right.

I will re open another topic when i'm done thinking about all this.

Thanks again
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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