how to check if the contact is filled in correctly with right or left join

Bjornlaat

We want to create a report to check that the correct contact is entered what with the relevant service provider but don't know how to go about this, would love your help with this. 

We are using 3 tables for this purpose: SHIPMENT, SHIPMENT_INVOLVED_PARTY, CONTACT

The shipment table has the following fields: 

SHIPMENT_GID, SERVPROV_GID

Example data:

123, 1

1234, 2

1245, 1

The SHIPMENT_INVOLVED_PARY table has the following fields:

SHIPMENT_GID, INVOLVED_PARTY_QUAL_GID, INVOLVED_PARTY_CONTACT_GID

Example data: 

123, CARRIER-OPS, AAA

1234, CARRIER-OPS, BBB

1245, CARRIER-OPS BBB

the CONTACT table has the following fields: 

CONTACT_GID, LOCATION_GID 

Example data: 

AAA, 123

abc, 123

abcc, 123

BBB, 1234

BB1, 1234

BB2, 1234

CCC, 1245

Based on the data in the database we can see that at shipment 1245 the wrong contact is filled in, contact BBB belongs to service provider 1234 and is filled in at a shipment with service provider 1245

The setup we have created is as follows: 

SELECT

S.SHIPMENT_GID,

SIP.INVOLVED_PARTY_QUAL_GID,

SIP.INVOLVED_PARTY_CONTACT_GID

FROM

SHIPMENT S,

SHIPMENT_INVOLVED_PARTY SIP

WHERE

S.SHIPMENT_GID = SIP.SHIPMENT_GID

AND SIP.INVOLVED_PARTY_QUAL_GID = 'RSK.CARRIER-OPS'

to this we need to add the check whether the value of SIP.INVOLVED_PARTY_CONTACT_GID corresponds to the value from the table Contact

To connect the table shipment with contact: servprov_gid = location_gid

Solomon Yakobson

SELECT  S.SHIPMENT_GID,
        SIP.INVOLVED_PARTY_QUAL_GID,
        SIP.INVOLVED_PARTY_CONTACT_GID
  FROM  SHIPMENT S,
        SHIPMENT_INVOLVED_PARTY SIP,
        CONTACT C
  WHERE S.SHIPMENT_GID = SIP.SHIPMENT_GID
    AND SIP.INVOLVED_PARTY_QUAL_GID = 'CARRIER-OPS'
    AND S.SHIPMENT_GID = C.LOCATION_GID(+)
    AND SIP.INVOLVED_PARTY_CONTACT_GID = C.CONTACT_GID(+)
    AND C.CONTACT_GID IS NULL
/

SY.

ora_1978

S.SHIPMENT_GID = SIP.SHIPMENT_GID

AND SIP.INVOLVED_PARTY_QUAL_GID = 'RSK.CARRIER-OPS'

--------------------

Added the join condition.

and SIP.INVOLVED_PARTY_CONTACT_GID = CONTACT.CONTACT_GID

-------------------

Bjornlaat

this does not help us, because it does not check whether the correct contact has been entered. the contact must be linked to the service provider. 

So we want to see all the shipments where the contact does not match the service provider. (all incorrectly entered contacts at the table SHIPMENT_INVOLVED_PARTY)

Solomon Yakobson

SELECT  S.SHIPMENT_GID,
        SIP.INVOLVED_PARTY_QUAL_GID,
        SIP.INVOLVED_PARTY_CONTACT_GID,
        NVL2(C.CONTACT_GID,'Existing INVOLVED_PARTY_CONTACT_GID','Non-existing INVOLVED_PARTY_CONTACT_GID') FLAG
  FROM  SHIPMENT S,
        SHIPMENT_INVOLVED_PARTY SIP,
        CONTACT C
  WHERE S.SHIPMENT_GID = SIP.SHIPMENT_GID
    AND SIP.INVOLVED_PARTY_QUAL_GID = 'CARRIER-OPS'
    AND S.SHIPMENT_GID = C.LOCATION_GID(+)
    AND SIP.INVOLVED_PARTY_CONTACT_GID = C.CONTACT_GID(+)
/

SY.

Bjornlaat

This is already a very good solution, thanks in advance!!!

But how can we see only the values that are not correct (Non-existing INVOLVED_PARTY_CONTACT_GID)? now we see them both? 

Thanks in advance for the answer. 

Solomon Yakobson

SELECT  S.SHIPMENT_GID,
        SIP.INVOLVED_PARTY_QUAL_GID,
        SIP.INVOLVED_PARTY_CONTACT_GID
  FROM  SHIPMENT S,
        SHIPMENT_INVOLVED_PARTY SIP,
        CONTACT C
  WHERE S.SHIPMENT_GID = SIP.SHIPMENT_GID
    AND SIP.INVOLVED_PARTY_QUAL_GID = 'CARRIER-OPS'
    AND S.SHIPMENT_GID = C.LOCATION_GID(+)
    AND SIP.INVOLVED_PARTY_CONTACT_GID = C.CONTACT_GID(+)
    AND C.CONTACT_GID IS NULL
/

SY.

Bjornlaat

Thankyou very much for the help @Solomon Yakobson !!!