Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

looping through the table shipment_stop matching in table location remark to output string based on match

BjornlaatMar 24 2023

Good morning, I need quite a bit of help with a query to get a certain result.

In brief, the following explanation: we have the table shipment. This is the start table. From this table we need to see if in the table SHIPMENT_STATUS the STATUS_VALUE_GID = RSK.PALLET_EXCHANGE_SOURCE_REQUIRED
Only these Shipment_gid what have this value should be shown.

Next, from the table SHIPMENT, the table SHIPMENT_STOP should be looked at. Note that there are always multiple records of 1 shipment in the table SHIPMENT_STOP. Next, from the SHIPMENT_STOP table, check whether a certain remark is present at the table LOCATION_REMARK, namely:
REMARK_QUAL_GID = RSK.PAKI AND
REMARK_TEXT =true

Next, the end result should display the following. We want to get from the shipment but line with:
SHIPMENT_GID, TEXT

The text has the following logic: if only the statusSTATUS_VALUE_GID = RSK.PALLET_EXCHANGE_SOURCE_REQUIRED and NOT REMARK_QUAL_GID = RSK.PAKI AND REMARK_TEXT =true then the text should display the following: We need pallet note for invoicing

When the status STATUS_VALUE_GID = RSK.PALLET_EXCHANGE_SOURCE_REQUIRED and REMARK_QUAL_GID = RSK.PAKI AND REMARK_TEXT =true then the text should display the following: Pallet note required for invoicing / bring empty pallets back to depot (like order)

Here some sample data

CREATE TABLE SHIPMENT
(
SHIPMENT_GID varchar(255),
TRANSPORT_MODE_GID varchar(255),
TOTAL_ACTUAL_COST int
);

CREATE TABLE SHIPMENT_STATUS
(
SHIPMENT_GID varchar(255),
STATUS_TYPE_GID varchar(255),
STATUS_VALUE_GID varchar(255)
);

CREATE TABLE SHIPMENT_STOP
(
SHIPMENT_GID varchar(255),
STOP_NUM int,
LOCATION_GID varchar(255)
);

CREATE TABLE LOCATION_REMARK
(
LOCATION_GID varchar(255),
REMARK_QUAL_GID varchar(255),
REMARK_TEXT varchar(255)
);

INSERT INTO SHIPMENT (SHIPMENT_GID, TRANSPORT_MODE_GID, TOTAL_ACTUAL_COST) VALUES ('RSK.2303230001', 'TL', '500');
INSERT INTO SHIPMENT (SHIPMENT_GID, TRANSPORT_MODE_GID, TOTAL_ACTUAL_COST) VALUES ('RSK.2303230002', 'OCEAN', '6500');
INSERT INTO SHIPMENT (SHIPMENT_GID, TRANSPORT_MODE_GID, TOTAL_ACTUAL_COST) VALUES ('RSK.2303230003', 'TL', '350');
INSERT INTO SHIPMENT (SHIPMENT_GID, TRANSPORT_MODE_GID, TOTAL_ACTUAL_COST) VALUES ('RSK.2303230004', 'HANDLING', '960');

INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230001', 'RSK.CANCELLED_SHIPMENT', 'RSK.CANCELLED_SHIPMENT_NO');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230001', 'RSK.ENROUTE', 'RSK.ENROUTE_NOT STARTED');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230001', 'RSK.FREIGHT_FORWARDING_CONFIRMATION', 'RSK.FFC_REPORT_NOT_SENT');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230001', 'RSK.PALLET_EXCHANGE_SOURCE', 'RSK.PALLET_EXCHANGE_SOURCE_REQUIRED');

INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230002', 'RSK.CANCELLED_SHIPMENT', 'RSK.CANCELLED_SHIPMENT_NO');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230002', 'RSK.ENROUTE', 'RSK.ENROUTE_COMPLETED');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230002', 'RSK.FREIGHT_FORWARDING_CONFIRMATION', 'RSK.FFC_REPORT_SENT');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230002', 'RSK.PALLET_EXCHANGE_SOURCE', 'RSK.PALLET_EXCHANGE_SOURCE_NOT_REQUIRED');

INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230003', 'RSK.CANCELLED_SHIPMENT', 'RSK.CANCELLED_SHIPMENT_NO');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230003', 'RSK.ENROUTE', 'RSK.ENROUTE_NOT STARTED');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230003', 'RSK.FREIGHT_FORWARDING_CONFIRMATION', 'RSK.FFC_REPORT_NOT_SENT');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230003', 'RSK.PALLET_EXCHANGE_SOURCE', 'RSK.PALLET_EXCHANGE_SOURCE_REQUIRED');

INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230004', 'RSK.CANCELLED_SHIPMENT', 'RSK.CANCELLED_SHIPMENT_NO');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230004', 'RSK.ENROUTE', 'RSK.ENROUTE_NOT STARTED');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230004', 'RSK.FREIGHT_FORWARDING_CONFIRMATION', 'RSK.FFC_REPORT_NOT_SENT');
INSERT INTO SHIPMENT_STATUS (SHIPMENT_GID, STATUS_TYPE_GID, STATUS_VALUE_GID) VALUES ('RSK.2303230004', 'RSK.PALLET_EXCHANGE_SOURCE', 'RSK.PALLET_EXCHANGE_SOURCE_REQUIRED');

INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230001', '1', 'RSK.NL-1011-AMSTERDAM');
INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230001', '2', 'RSK.NL-1114-AMSTERDAM-DUIVENDRECHT');
INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230002', '1', 'RSK.NL-1121-LANDSMEER');
INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230002', '2', 'RSK.NL-1165-HALFWEG');

INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230003', '1', 'RSK.NL-1011-AMSTERDAM');
INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230003', '2', 'RSK.NL-1165-HALFWEG');
INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230003', '3', 'RSK.NL-1121-LANDSMEER');
INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230003', '4', 'RSK.NL-1114-AMSTERDAM-DUIVENDRECHT');

INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230004', '1', 'RSK.NL-1181-AMSTELVEEN');
INSERT INTO SHIPMENT_STOP (SHIPMENT_GID, STOP_NUM, LOCATION_GID) VALUES ('RSK.2303230004', '2', 'RSK.NL-1394-NEDERHORST DEN BERG');

INSERT INTO LOCATION_REMARK (LOCATION_GID, REMARK_QUAL_GID, REMARK_TEXT) VALUES ('RSK.NL-1011-AMSTERDAM', 'RSK.PAKI', 'false');
INSERT INTO LOCATION_REMARK (LOCATION_GID, REMARK_QUAL_GID, REMARK_TEXT) VALUES ('RSK.NL-1011-AMSTERDAM', 'RSK.INITIALS', 'RE');
INSERT INTO LOCATION_REMARK (LOCATION_GID, REMARK_QUAL_GID, REMARK_TEXT) VALUES ('RSK.NL-1114-AMSTERDAM-DUIVENDRECHT', 'RSK.PAKI', 'true');
INSERT INTO LOCATION_REMARK (LOCATION_GID, REMARK_QUAL_GID, REMARK_TEXT) VALUES ('RSK.NL-1114-AMSTERDAM-DUIVENDRECHT', 'RSK.INITIALS', 'BJ');
INSERT INTO LOCATION_REMARK (LOCATION_GID, REMARK_QUAL_GID, REMARK_TEXT) VALUES ('RSK.NL-1121-LANDSMEER', 'RSK.PAKI', 'false');
INSERT INTO LOCATION_REMARK (LOCATION_GID, REMARK_QUAL_GID, REMARK_TEXT) VALUES ('RSK.NL-1121-LANDSMEER', 'RSK.INITIALS', 'BJ');

The result that the data and the requirements shoud give:

SHIPMENT_GID, TEXT

RSK.2303230001, Pallet note required for invoicing / bring empty pallets back to depot (like order)

RSK.2303230003,Pallet note required for invoicing / bring empty pallets back to depot (like order)

RSK.2303230004, We need pallet note for invoicing

RSK.2303230002 should not be visible because it has not the correct status
This post has been answered by Barbara Boehmer on Mar 24 2023
Jump to Answer
Comments
Post Details
Added on Mar 24 2023
#bi, #oracle, #pl-sql, #plsql, #query, #reports, #sql
3 comments
237 views
3 mentions