Inserting R or Y value in a shipment stop depending on location refnum

Rob Mengelers · Aug 1, 2022 9:40AM

Hi all!

Im struggling with writing a SQL query for an automation agent in OTM Cloud.

What I want to achieve is that, based on the value of the corrospondig location refnum, a value is inserted on that shipment stop's attribute1.

So I have a shipment which has 3 stops, 2 pickup stops and 1 delivery stop. For the first pickup stop the location has 2 refnums: SLOT_BOOKING_LOADING and SLOT_BOOKING_UNLOADING. Because that location is assigned to be a pickup stop (P) we want to look at the SLOT_BOOKING_LOADING refnum instead of SLOT_BOOKING_UNLOADING and use that value to determine if a R or Y should be inserted in attribute1 for that shipment stop.

So if that location, which for this shipment is the first pickup stop, has the value for SLOT_BOOKING_LOADING: SLOT_BOOKING_LOADING_REQUIRED then we want to assign a Y to attribute1 for that shipment stop.

For the second shipment stop, which is also a pickup stop, we again look at the corrosponding location. Because this stop is also P we again want to look at the SLOT_BOOKING_LOADING refnum on the location. This refnum now has the value SLOT_BOOKING_LOADING_NOT_REQUIRED, so we want to assign a R to attribute1 for that shipment stop.

Last but not least for the last stop, which is a delivery stop, we again look at the corropsonding location. Now, because this stop is a D we want to look at the SLOT_BOOKING_UNLOADING refnum on that location. This refnum appears to hold SLOT_BOOKING_UNLOADING_NOT_REQUIRED, so we want to assign a R to attribute1 for that shipment stop.

I hope I made my issue clear and look forward to what kind of solutions could be applied here :)

I preferably want to solve this using a single query

Frank Kulash · Aug 2, 2022 2:38PM

Hi, @Rob Mengelers

Regarding the insert statements I could not figure out what I did wrong.

Al the statements are missing semicolons at the end. (Perhpas you're using a front end that doesn't require them.)

This problem would be much simpler if the location_refnum table looked like this:

                                          STOP
LOCATION_GID                              _TYPE ATTRIBUTE1
----------------------------------------- ----- ----------
RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT P     Y
RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT D     Y
RSK.MERWE-BAD-FRIEDRICHSHALL              P     R
RSK.MERWE-BAD-FRIEDRICHSHALL              D     R

where stop_type is derived from location_refnum_qual_gid and attriubute1 is derived from location_refnum_value. The sub-query called src in the statement below does just that:

MERGE INTO shipment_stops dst
USING (
	 SELECT location_gid
	 ,	 CASE location_refnum_qual_gid
	 	   WHEN 'RSK.SLOT_BOOKING_LOADING'   THEN 'P'
	 	   WHEN 'RSK.SLOT_BOOKING_UNLOADING' THEN 'D'
	 	 END AS stop_type
	 , 	 CASE
	 	   WHEN location_refnum_value LIKE '%\_NOT\_REQUIRED:' ESCAPE '\' THEN 'R'
	 	   WHEN location_refnum_value LIKE      '%\_REQUIRED:' ESCAPE '\' THEN 'Y'
		 END AS attribute1
	 FROM	 location_refnum
	 WHERE	 location_refnum_qual_gid IN ( 'RSK.SLOT_BOOKING_LOADING'
	 	 			     , 'RSK.SLOT_BOOKING_UNLOADING'
					     )
)			  src
ON (     dst.location_gid = src.location_gid
   AND	 dst.stop_type	  = src.stop_type
   )
WHEN MATCHED THEN UPDATE
SET          dst.attribute1 = src.attribute1
WHERE LNNVL (dst.attribute1 = src.attribute1)	-- for efficiency
;

After running this, the shipment_stops table looks like this:

                                                          STOP
SHIPMENT_GID    LOCATION_GID                              _TYPE ATTRIBUTE1
--------------- ----------------------------------------- ----- ----------
RSK.2205250003  RSK.MERWE-BAD-FRIEDRICHSHALL              D     R
RSK.2205250003  RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT P     Y

This assumes that the combination (location_gid, location_refnum_qual_gid) is unique in the location_refnum table, and that you'll never need to set shipment_stops.attribute1 to NULL when it already has a value. (If either of those assumptions are wrong, then only minor changes are needed.)

This needs to be modulair enough in case we have more than 2 shipment stops, this will apply to all of them.

No problem; the MERGE statement above does what you requested no matter how many rows are in the shopment_stops table.

Frank Kulash · Aug 1, 2022 1:54PM

Hi, @Rob Mengelers

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. For DML issues (such as INSERT) the CREATE TABLE and INSERT statements you post should show the tables as they exist before the DML begins, and the results will be the contents of the changed table after the DML is finished. Always post your complete Oracle version (e.g. 18.4.0.0.0).

BluShadow · Aug 1, 2022 2:33PM

As Frank says, we'd need details of your tables and some example data and expected results etc.

We don't have your database or work in the same area of business, to know all the terminology or what possible values/scenarios can occur, so make sure your example data covers each of the different things that can happen and explain why the results should be the way you want them. Sure, we all know about delivery of goods and the fact it has to be sent for delivery (loaded) to booked slots, perhaps go through various stops and eventually be delivered, but that doesn't mean we all know your system.

From what you've described, you have some data on a table that you want to reference to other tables depending on existing data and then update the data based on the current state of the overall delivery. That sounds like a MERGE statement which is using a query that has outer joins in it. But that's about as detailed as we could surmise so far.

Rob Mengelers · Aug 2, 2022 12:04PM

Hi Frank and BluShadow,

Thanks a ton for the explanation! I will make sure to include them next time :)

As for now, we have 2 tables. 1 is called SHIPMENT_STOPS and 1 is called LOCATION_REFNUM. They can be created using the following script. Keep in mind, I dont know the datatypes which are used by OTM, so therefore I all made them varchars

CREATE SHIPMENT_STOPS (

SHIPMENT_GID varchar(200),
LOCATION_GID varchar(200),
STOP_TYPE varchar(200),
ATTRIBUTE1 varchar(200)
)

CREATE LOCATION_REFNUM (

LOCATION_GID varchar(200),
LOCATION_REFNUM_QUAL_GID varchar(200),
LOCATION_REFNUM_VALUE varchar(200)
)

Lets assume we insert the following data

INSERT INTO SHIPMENT_STOPS VALUES ('RSK.2205250003', 'RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT', 'P', null)
INSERT INTO SHIPMENT_STOPS VALUES ('RSK.2205250003', 'RSK.MERWE-BAD-FRIEDRICHSHALL', 'D', null)

INSERT INTO LOCATION_REFNUM VALUES ('RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT', 'RSK.SLOT_BOOKING_LOADING', 'Y:RSK.SLOT_BOOKING_LOADING:RSK.SLOT_BOOKING_LOADING_REQUIRED:')
INSERT INTO LOCATION_REFNUM VALUES ('RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT', 'RSK.SLOT_BOOKING_UNLOADING', 'Y:RSK.SLOT_UNBOOKING_LOADING:RSK.SLOT_BOOKING_UNLOADING_REQUIRED:')
INSERT INTO LOCATION_REFNUM VALUES ('RSK.MERWE-BAD-FRIEDRICHSHALL', 'RSK.SLOT_BOOKING_LOADING', 'Y:RSK.SLOT_BOOKING_LOADING:RSK.SLOT_BOOKING_LOADING_NOT_REQUIRED:')
INSERT INTO LOCATION_REFNUM VALUES ('RSK.MERWE-BAD-FRIEDRICHSHALL', 'RSK.SLOT_BOOKING_UNLOADING', 'Y:RSK.SLOT_UNBOOKING_LOADING:RSK.SLOT_BOOKING_UNLOADING_NOT_REQUIRED:')

We can see that both the shipment stops have attribute1 set to null. These are the values we want to see filled.

So in this case, because the first shipment stop has the STOP_TYPE value of P, it should look at its related location refnum table and see if the SLOT_BOOKING_LOADING is set to required or not. In this case we can see that the value of that refnum is set to Y:RSK.SLOT_BOOKING_LOADING:RSK.SLOT_BOOKING_LOADING_REQUIRED: meaning its required. Therefore we want to have inserted in ATTRIBUTE1 of that shipment stop the value 'Y'

For the second shipment stop, its STOP_TYPE is D. Again looking at its related location refnum we should then look at the refnum SLOT_BOOKING_UNLOADING, since its D. The value of that refnum is Y:RSK.SLOT_UNBOOKING_LOADING:RSK.SLOT_BOOKING_UNLOADING_NOT_REQUIRED: meaning that it is not required. Therefore we want to have inserted in ATTRIBUTE1 of that shipment stop the value 'R'.

This needs to be modulair enough in case we have more than 2 shipment stops, this will apply to all of them.

So in this specific example, the result we would want to see should look like the following created table:

INSERT INTO SHIPMENT_STOPS VALUES ('RSK.2205250003', 'RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT', 'P', 'Y')
INSERT INTO SHIPMENT_STOPS VALUES ('RSK.2205250003', 'RSK.MERWE-BAD-FRIEDRICHSHALL', 'D', 'R')

The reason we want this is to determine if we need to register a slot booking at that specific location. The R and Y corrolate to indicators used in the screensets. This way the planners know what is still left to book and what is not required.

My complete Oracle version is:

Software Version: OTMv22b
Weekly Patch Version: 20220701
Database Edition: OTM22B
Cloud Infrastruture: Gen2

Frank Kulash · Aug 2, 2022 1:32PM

hI, @Rob Mengelers

Thanks for posting the CREATE TABLE and INSERT statements. I know that can be trouble and take time. Rmember why you heed to do that: so that the people who want to help you can re-create the problem and test their ideas. If you post CREATE TABLE and INSERT statements that don't work, you're just wasting everyone's time, including you own. None of the statements you posted work. Please test (and, if necessary, fix) your statments before you post them.

Will you always want to set attribute1 = 'R' whenever the appropriate location_refnum_value ends in '_NOT_REQUIRED', and to 'Y' when the appropriate location_refnum_value ends in '_REQUIRED' where that is not preceeded by '_NOT'?

Rob Mengelers · Aug 2, 2022 1:58PM

Hi Frank,

Im so very sorry. The create statements should be

CREATE TABLE SHIPMENT_STOPS (

SHIPMENT_GID varchar(200),
LOCATION_GID varchar(200),
STOP_TYPE varchar(200),
ATTRIBUTE1 varchar(200)
)

CREATE TABLE LOCATION_REFNUM (

LOCATION_GID varchar(200),
LOCATION_REFNUM_QUAL_GID varchar(200),
LOCATION_REFNUM_VALUE varchar(200)
)

Regarding the insert statements I could not figure out what I did wrong. My SQL knowledge is fairly limited.

Then to anwser your question, I indeed would always want to see 'R' inserted when de refnum value ends in 'NOT_REQUIRED' and to 'Y' when the appropriate location_refnum_value ends in '_REQUIRED' where that is not preceeded by '_NOT'!

Frank Kulash · Aug 2, 2022 2:38PM

Hi, @Rob Mengelers

Regarding the insert statements I could not figure out what I did wrong.

Al the statements are missing semicolons at the end. (Perhpas you're using a front end that doesn't require them.)

This problem would be much simpler if the location_refnum table looked like this:

                                          STOP
LOCATION_GID                              _TYPE ATTRIBUTE1
----------------------------------------- ----- ----------
RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT P     Y
RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT D     Y
RSK.MERWE-BAD-FRIEDRICHSHALL              P     R
RSK.MERWE-BAD-FRIEDRICHSHALL              D     R

where stop_type is derived from location_refnum_qual_gid and attriubute1 is derived from location_refnum_value. The sub-query called src in the statement below does just that:

MERGE INTO shipment_stops dst
USING (
	 SELECT location_gid
	 ,	 CASE location_refnum_qual_gid
	 	   WHEN 'RSK.SLOT_BOOKING_LOADING'   THEN 'P'
	 	   WHEN 'RSK.SLOT_BOOKING_UNLOADING' THEN 'D'
	 	 END AS stop_type
	 , 	 CASE
	 	   WHEN location_refnum_value LIKE '%\_NOT\_REQUIRED:' ESCAPE '\' THEN 'R'
	 	   WHEN location_refnum_value LIKE      '%\_REQUIRED:' ESCAPE '\' THEN 'Y'
		 END AS attribute1
	 FROM	 location_refnum
	 WHERE	 location_refnum_qual_gid IN ( 'RSK.SLOT_BOOKING_LOADING'
	 	 			     , 'RSK.SLOT_BOOKING_UNLOADING'
					     )
)			  src
ON (     dst.location_gid = src.location_gid
   AND	 dst.stop_type	  = src.stop_type
   )
WHEN MATCHED THEN UPDATE
SET          dst.attribute1 = src.attribute1
WHERE LNNVL (dst.attribute1 = src.attribute1)	-- for efficiency
;

After running this, the shipment_stops table looks like this:

                                                          STOP
SHIPMENT_GID    LOCATION_GID                              _TYPE ATTRIBUTE1
--------------- ----------------------------------------- ----- ----------
RSK.2205250003  RSK.MERWE-BAD-FRIEDRICHSHALL              D     R
RSK.2205250003  RSK.LAURA STAALSERVICE-NL-6222-MAASTRICHT P     Y

This assumes that the combination (location_gid, location_refnum_qual_gid) is unique in the location_refnum table, and that you'll never need to set shipment_stops.attribute1 to NULL when it already has a value. (If either of those assumptions are wrong, then only minor changes are needed.)

This needs to be modulair enough in case we have more than 2 shipment stops, this will apply to all of them.

No problem; the MERGE statement above does what you requested no matter how many rows are in the shopment_stops table.

Rob Mengelers · Aug 3, 2022 12:54PM

Hi Frank,

This seems to be working perfect! I have another addition I want to apply to this query tho.

Is it possible to execute this query for 1 shipment in particular? So we have an agent running on shipment create, and for that particular shipment we create I also want to run this query.

So lets assume the GID of the shipment that is being created is mapped to $GID, how would I incorperate that WHERE statement in this query?

Thanks a lot in advance

Frank Kulash · Aug 3, 2022 1:23PM

Hi, @Rob Mengelers

So lets assume the GID of the shipment that is being created is mapped to $GID, how would I incorperate that WHERE statement in this query?

So, you only want to include rows from shipment_stops that meet a certain condition. Add that condition to the WHERE clause that affects shipment_stop. For example:

MERGE INTO shipment_stops dst
USING (
	 SELECT location_gid
	 ,	 CASE location_refnum_qual_gid
	 	   WHEN 'RSK.SLOT_BOOKING_LOADING'   THEN 'P'
	 	   WHEN 'RSK.SLOT_BOOKING_UNLOADING' THEN 'D'
	 	 END AS stop_type
	 , 	 CASE
	 	   WHEN location_refnum_value LIKE '%\_NOT\_REQUIRED:' ESCAPE '\' THEN 'R'
	 	   WHEN location_refnum_value LIKE      '%\_REQUIRED:' ESCAPE '\' THEN 'Y'
		 END AS attribute1
	 FROM	 location_refnum
	 WHERE	 location_refnum_qual_gid IN ( 'RSK.SLOT_BOOKING_LOADING'
	 	 			    , 'RSK.SLOT_BOOKING_UNLOADING'
					    )
)			  src
ON (     dst.location_gid = src.location_gid
   AND	 dst.stop_type	  = src.stop_type
   )
WHEN MATCHED THEN UPDATE
SET          dst.attribute1 = src.attribute1
WHERE LNNVL (dst.attribute1 = src.attribute1)	-- for efficiency
AND  dst.shipment_gid = :target_shipment_gid -- ***** THIS LINE IS NEW *****
;

Only one line, at the very end, has been added; the rest of the statement is unchanged from yesterday.

I tested this in SQL*Plus, using a bind variable called :target_shipment_gid. If $GID is a valid variable in your front end, you can use it where I used :target_shipment_gid.

Forum Stats

Quick Links

Categories

Inserting R or Y value in a shipment stop depending on location refnum

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Inserting R or Y value in a shipment stop depending on location refnum

Best Answer

Answers