Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

Oracle SQL - Query help

I have a table (Receiver_Detail) that have the following information.

User generated image
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

Open in new window


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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

125	PO112	1
126	PO113	1
127	PO115	3

Open in new window


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.

--
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

--

Open in new window



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

--

Open in new window


Hope it helps!
Avatar of holemania
holemania

ASKER

Thank you for the help, but I am still having issue.  See below and I will try to explain.

User generated image
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
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Awesome.  That is exactly what I am looking for.  Thank you!!!