Select Statement Problem

[lasd]

Hi
I am trying to get the receipt num and the quantity_received value to appear once and not to multiply all the way down when it is connected to the other table. Is my order by statement correct or should i be doing something else?
thanks very much in advance for the help
kindest regards
lasd

select distinct(rsh.RECEIPT_NUM),
rsl.QUANTITY_RECEIVED,
rsl.PO_LINE_ID
from rcv_transactions rtxn,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
where rtxn.SHIPMENT_LINE_ID=rsl.SHIPMENT_LINE_ID
and rtxn.TRANSACTION_TYPE='DELIVER'
order by rsh.RECEIPT_NUM, rsl.QUANTITY_RECEIVED

 

[PHV]

rcv_shipment_headers is not joined ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lasd]

and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)

sorry i just cut out some of the code as i was pasting it in to the thread. this is the line in which i join rcv_shipment_headers....

 

[PHV]

So, what is the problem ?
What is the actual result vs the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lasd]

hi

i will just give you an example of what is coming up. I am linking this to the other tables for PO's using the po_line_id... i am getting the po number and the invoice number belonging to this PO number.then i want to show the receipt number and the receipt qty. but here is whats happening.

invoice no rec no rec qty
23432423 78 100
23495877 78 100
45325325 78 100

i want it just to show once the rec no and the rec qty
invoice no rec no rec qty
23432423 78 100
23495877
45325325

 

[PHV]

It's a report issue, not a SQL one.