Join to return the first instance - cannot use group by
Posted by:
m m
Date: September 09, 2014 03:35PM
Hello
I have the following subquery where i need to use the first instance of each invoice per job. for example
each consignment can have multiple invoices;
consignment Invoice
1 111
1 112
2 113
3 114
3 115
I'm only interested in the first instance of each invoice per consignment.e.g.
1 111
2 113
3 114
I have tried adding group by CONSIGNMENT_ALL_HEADER_UNS.OPSREF$$ to the end of this query, however it is causing the query to run for a very long time and doesn't complete
I believe I need to do something with the JOIN , however I have tried the various join types , right / left / inner / outter none of which change the results.
I'm not very experienced with complicated queries and I cannot figure this out.
Any ideas?
SET @START_DATE := "2014-08-27";
SET @END_DATE:= "2014-09-02";
SELECT distinct
CONSIGNMENT_ALL_HEADER_UNS.OPSREF$$
FROM
CONSIGNMENT_ALL_HEADER_UNS
JOIN INVOICE_HEADER_UNS ON CONSIGNMENT_ALL_HEADER_UNS.OPSREF$$ = INVOICE_HEADER_UNS.OUR_REF
WHERE
CONSIGNMENT_ALL_HEADER_UNS.TMODE$$ = '6'
AND TRANSACTION_STATUS = 9
#AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ not in ('1' , '4', '8', '7')
AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ <>'7'
AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ in ('3' , '5', '9')
AND (@START_DATE IS NULL OR INVOICE_HEADER_UNS.INVOICE_DATE_D8>=@START_DATE) AND
(@END_DATE IS NULL OR INVOICE_HEADER_UNS.INVOICE_DATE_D8<=@END_DATE OR INVOICE_HEADER_UNS.INVOICE_DATE_D8 IS NULL)
#AND (@START_DATE IS NULL OR SUPPLIER_COSTS_UNS.ESTIMATE_DATE >=(@START_DATE)) AND
#(@END_DATE IS NULL OR SUPPLIER_COSTS_UNS.ESTIMATE_DATE <=@END_DATE OR SUPPLIER_COSTS_UNS.ESTIMATE_DATE IS NULL))
Subject
Written By
Posted
Join to return the first instance - cannot use group by
September 09, 2014 03:35PM
September 09, 2014 04:54PM
September 10, 2014 04:11AM
September 10, 2014 10:27AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.