Good morning,
I am having some problems with my current query, namely that duplicate values are being counted. This is, because in a pair table of two tables, values can appear twice.
The current query is as follows:
SELECT
BUY.SHIPMENT_XID BUYSHIPMENT,
SELL.SHIPMENT_XID SELLSHIPMENT
FROM
SHIPMENT SELL,
V30_SHIPMENT_ORDER_RELEASE V30,
V30_SHIPMENT_ORDER_RELEASE V300,
SHIPMENT BUY
WHERE
SELL.PERSPECTIVE = 'S
AND SELL.SHIPMENT_GID = V30.SHIPMENT_GID
AND V30.ORDER_RELEASE_GID = V300.ORDER_RELEASE_GID
AND V300.PERSPECTIVE = 'B
AND V300.SHIPMENT_GID = BUY.SHIPMENT_GID
In the table shipment there is only the shipment number as shipment_gid and perspective indicating whether it is a buy shipment or a sellshipment via B or S
Example data:
SHIPMENT_GID, PERSPECTIVE
1, B
2, S
3, B
4, S
To link a buy shipment to a sellshipment, oracle has the table: V30_SHIPMENT_ORDER_RELEASE. Here 3 fields are available: PERSPECTIVE, SHIPMENT_GID and ORDER_RELEASE_GID. By means of an ORDER_RELEASE_GID (or several) buy shipments are linked to a sellshipment.
Example data:
SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID
1, B, RELEASE1
2, S, RELEASE1
3, B, RELEASE2
3, B, RELEASE3
4, S, RELEASE2
4, S, RELEASE3
the result the above query should give is:
BUYSHIPMENT, SELLSHIPMENT
1, 2
3,4
the current query returns the following result
BUYSHIPMENT, SELLSHIPMENT
1, 2
3,4
3,4