holemania
asked on
Oracle SQL - Query help
I have a table (Receiver_Detail) that have the following information.
So I am trying to write a sql query that should only pull lines that has no return. Example from above, PO# PO112 has been received, returned to the vendor, and then received again. The first 2 lines cancelled each other out and I do not want those 2 lines to show on my report. I only want receiver ID 125 to show up. Issue is if I do a sum and group by PO#, I would get the 1, but it is also critical I get the actual receiver ID with it as well.
The above would return a PO# PO112 with Qty 1, but not sure how I can include the receiver ID 125 for reference. Any ideas?
So I am trying to write a sql query that should only pull lines that has no return. Example from above, PO# PO112 has been received, returned to the vendor, and then received again. The first 2 lines cancelled each other out and I do not want those 2 lines to show on my report. I only want receiver ID 125 to show up. Issue is if I do a sum and group by PO#, I would get the 1, but it is also critical I get the actual receiver ID with it as well.
SELECT PO_ID, SUM(QTY) QTY FROM RECEIVER_DETAIL GROUP BY PO_ID
The above would return a PO# PO112 with Qty 1, but not sure how I can include the receiver ID 125 for reference. Any ideas?
can you also post your expected results?
try:
SELECT max(RECEIVER_ID) RECEIV_ID, PO_ID, SUM(QTY) QTY FROM RECEIVER_DETAIL GROUP BY PO_ID
We could fine tune this later if it fails under certain conditions not discussed yet.
Mike
SELECT max(RECEIVER_ID) RECEIV_ID, PO_ID, SUM(QTY) QTY FROM RECEIVER_DETAIL GROUP BY PO_ID
125 PO112 1
126 PO113 1
127 PO115 3
We could fine tune this later if it fails under certain conditions not discussed yet.
Mike
Hi holemania,
Just created a solution for you. With this solution you can fetch all the colums from the table.
Output
Hope it helps!
Just created a solution for you. With this solution you can fetch all the colums from the table.
--
SELECT [RECEIVER_ID], [Reciever Line] , [PO_ID] , [PO Line] , Qty1 Qty FROM
(
SELECT [RECEIVER_ID], [Reciever Line] , [PO_ID] , [PO Line] , SUM([QTY]) OVER (PARTITION BY [PO_ID]) Qty1 , ROW_NUMBER() OVER (PARTITION BY [PO_ID] ORDER BY [RECEIVER_ID] DESC) rnk FROM RECEIVER_DETAIL
)k WHERE rnk = 1
--
Output
--
/*------------------------
OUTPUT
------------------------*/
RECEIVER_ID Reciever Line PO_ID PO Line Qty
----------- ------------- ---------------------------------------------------------------------------------------------------- ----------- -----------
125 1 PO 112 1 1
126 1 PO 113 1 1
127 1 PO 115 1 3
--
Hope it helps!
ASKER
Thank you for the help, but I am still having issue. See below and I will try to explain.
So from above, when a PO is received, if more than one line is received at once, it is the same receiver ID with different line_no for the receiver line. If it's received at different times, it shows up with different receiver ID and number of lines it was received.
The issue is that the report I am generating has a lot of returns. My users do not want to see these returns, but what is actually received. The issue is they want to see the receiver ID with what's received as well. If not then, I can just sum and it will calculate correctly with what's received.
From the above example, the highlighted green is what they want shown on the report. The first PO1121, it was received, then returned, and then received again. So the 3rd line down is what they want to see in the report. The second PO1020, line 1 and 2 were both received at the same time hence the same receiver ID. However, line 2 of the PO was returned, henced they only want to see what's received into inventory which is line 1 of PO1020.
I tried what's suggested by Pawan Kumar, which will work if it's consistent pattern, but it's not. So now I'm stuck how to get what's actually received (not returned), and the receiver ID.
So from above, when a PO is received, if more than one line is received at once, it is the same receiver ID with different line_no for the receiver line. If it's received at different times, it shows up with different receiver ID and number of lines it was received.
The issue is that the report I am generating has a lot of returns. My users do not want to see these returns, but what is actually received. The issue is they want to see the receiver ID with what's received as well. If not then, I can just sum and it will calculate correctly with what's received.
From the above example, the highlighted green is what they want shown on the report. The first PO1121, it was received, then returned, and then received again. So the 3rd line down is what they want to see in the report. The second PO1020, line 1 and 2 were both received at the same time hence the same receiver ID. However, line 2 of the PO was returned, henced they only want to see what's received into inventory which is line 1 of PO1020.
I tried what's suggested by Pawan Kumar, which will work if it's consistent pattern, but it's not. So now I'm stuck how to get what's actually received (not returned), and the receiver ID.
by the way... do you have a column in your table called as "Comment" ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome. That is exactly what I am looking for. Thank you!!!