Oracle - max date filter issue

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
LVL 11
Wilder1626Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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
 
awking00Connect With a Mentor Commented:
>>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
 
Wilder1626Author Commented:
Oh sure.

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

Thanks again
resources-TABLE.xls
resources-result.xls
0
 
Wilder1626Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.