We want to create a case when for a query.
In the case when, we need to check whether CARRIER-OPS is present in SHIPMENT_INVOLVED_PARTY. If it is, the CARRIER-OPS value should be looked up in the CONTACT table and its name displayed.
If the CARRIER-OPS is not present the value of the service provider should be displayed.
example data:
TABLE: SHIPMENT
SHIPMENT_GID, SERVICE PROVIDER
1, SERVICEPROVIDER5
2, SERVICEPROVIDER5
3, SERVICE PROVIDER8
4, SERVICE PROVIDER2
TABLE: SHIPMENT_INVOLVED_PARTY
SHIPMENT_GID, INVOLVED_PARTY_QUAL_GID, INVOLVED_PARTY_CONTACT_GID
1, CARRIER-OPS, 51297
1, LOGISTICS, 40 CELL DIVISION
2, LOGISTICS, 50 CELL DIVISION
3, CARRIER-OPS, 51297
4, LOGISTICS, 40 CELL DIVISION
TABLE: CONTACT
INVOLVED_PARTY_CONTACT_GID, NAME
51297, ABC
elaboration:
SHIPMENT_GID: 1 and 2 should be given the value ABC
SHIPMENT_GID 2 must get the value SERVICEPROVIDER5
SHIPMENT_GID 4 must get the value SERVICEPROVIDER2
If there are any questions please let me know