Home » SQL & PL/SQL » SQL & PL/SQL » Showing right values with max function
Showing right values with max function [message #323478] Wed, 28 May 2008 09:53 Go to next message
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? Smile

Re: Showing right values with max function [message #323479 is a reply to message #323478] Wed, 28 May 2008 09:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I attach my query in in query.txt file.

No you don't Cool
Re: Showing right values with max function [message #323480 is a reply to message #323479] Wed, 28 May 2008 10:00 Go to previous messageGo to next message
gosi
Messages: 3
Registered: May 2008
Junior Member
Strange, I try again
  • Attachment: query.txt
    (Size: 3.37KB, Downloaded 185 times)
Re: Showing right values with max function [message #323481 is a reply to message #323478] Wed, 28 May 2008 10:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Showing right values with max function [message #323507 is a reply to message #323481] Wed, 28 May 2008 11:05 Go to previous messageGo to next message
gosi
Messages: 3
Registered: May 2008
Junior Member
Hey Kevin, tanks for the reply.

I removed the MS table, then I got the correct date I was looking for.


I attach the select clause I changed in query2.txt.

Now when I run the total query it doesn't give me any rows, not quite sure why it is empty, any ideas?
  • Attachment: query2.txt
    (Size: 1.36KB, Downloaded 190 times)
Re: Showing right values with max function [message #323534 is a reply to message #323478] Wed, 28 May 2008 12:37 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
When faced with no rows returned and I don't know why, I always take 30 minutes to do the query one piece at a time. Whittle the query down on step at a time till rows start comming back, or go in reverse and start with a small piece and keep adding tables and joins till no rows show up. then you will know where the rows are falling out.

Using the WITH clause can often make this easier.

Kevin Meade's OraFAQ Blog

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

Good luck, Kevin
Previous Topic: How do I view the package's body
Next Topic: ORA-03134
Goto Forum:
  


Current Time: Fri Dec 02 18:50:51 CST 2016

Total time taken to generate the page: 0.12644 seconds