Forum Stats

  • 3,826,362 Users
  • 2,260,636 Discussions
  • 7,896,916 Comments

Discussions

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

Bjornlaat
Bjornlaat Member Posts: 18 Green Ribbon

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

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond
    Answer ✓
    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.

Answers

  • ora_1978
    ora_1978 Member Posts: 522 Bronze Badge

    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
    Bjornlaat Member Posts: 18 Green Ribbon

    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
    Solomon Yakobson Member Posts: 19,471 Red Diamond
    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
    Bjornlaat Member Posts: 18 Green Ribbon

    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
    Solomon Yakobson Member Posts: 19,471 Red Diamond
    Answer ✓
    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
    Bjornlaat Member Posts: 18 Green Ribbon

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