Showing right values with max function [message #323478] |
Wed, 28 May 2008 09:53  |
gosi
Messages: 3 Registered: May 2008
|
Junior Member |
|
|
What I am trying to achieve here is to show only one row for each item number(segment1) that is chosen. I would like the query to give me the latest row and use the latest creation_date to achieve that.
I'm trying to add a select statement into a query but it's not returning any values. I think I am doing something wrong in the /*snippet*/ part, but can't figure out what.
I attach my query in in query.txt file.
Any ideas? 
|
|
|
|
|
Re: Showing right values with max function [message #323481 is a reply to message #323478] |
Wed, 28 May 2008 10:09   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
I think you are missing a join in your grouping query that generates your max date:
SELECT ms.Segment1,
MAX(ms.Creation_Date) AS MaxDate
FROM apps.mtl_System_Items_b ms,
apps.mtl_System_Items_b msi,
apps.po_Lines_All Pl,
apps.mtl_CatEgories_b mc,
apps.Per_People_f Can,
apps.Per_People_f cLo
WHERE Pl.Item_Id = msi.Inventory_Item_Id (+)
AND msi.Organization_Id (+) = 28
AND Pl.Category_Id = mc.Category_Id (+)
AND Pl.CancelLed_By = Can.Person_Id (+)
AND Can.Effective_Start_Date (+) <= Trunc(SYSDATE)
AND Can.Effective_End_Date (+) >= Trunc(SYSDATE)
AND Pl.Closed_By = cLo.Person_Id (+)
AND cLo.Effective_Start_Date (+) <= Trunc(SYSDATE)
AND cLo.Effective_End_Date (+) >= Trunc(SYSDATE)
GROUP BY ms.Segment1
Where is the join for MS? All tables in your from clause are hooked up, but not the MS table. Without it, this query makes no sense unless you can explain it to us. Put the missing join in and try again. Also, good job on the outer-joins, none missing there.
Kevin
|
|
|
|
|