Using SQL to update a column with the greater value of two other columns

I received a call to action from my superior. "The Order Close Date in the Order History file has been messed up. Can you write a program to fix it?"

"When do you want this for?" I replied.

"Next week" he shrugged.

I have more than enough time, four days, to write a RPG program, but where is the fun in that when I could do something with SQL? Especially when this is something I have never done before: compare two values and use the greater to update another value.

Let me explain the files and fields I will be using in the examples:

File	Fields
ORDHST Order History	ORDNO - Order number (key 1)
	ENTRDT - Order entered date (key 2)
	CLOSDT - Order closed date
ORDHDT Order Detail History	ORDNO - Order number (key 1)
	ENTRDT - Order entered date (key 2)
	LASTDT - Last activity date
	CLOSDT - Line closed date

All the "date" fields are 7,0 packed numeric in CYMD format.

I need to take the greatest value in either LASTDT or CLOSDT from all the lines for each order in the Order Detail History file, and update the CLOSDT field in the Order History.

I am going to break this into two steps:

1. Create a History file containing all the values before I make the change.
2. Update the Order Header with the appropriate date.

 

Create history file

The History file will have the following fields/columns:

• ORDNBR from Order Header
• ENTRDT from Order Header
• CLOSDT from Order Header
• Greatest value in LASTDT for this order from Order Detail
• Greatest value in CLOSDT for this order from Order Detail
• The greater value of the two greatest values

I can create this file/table using SQL's CREATE TABLE. There is more detail on the CREATE TABLE in an earlier post here.

01 CREATE TABLE QTEMP.HISTORY AS 02 (SELECT A.ORDNBR, 03 A.ENTRDT, 04 A.CLOSDT, 05 MAX(B.LASTDT) AS "LASTDATE", 06 MAX(B.CLOSDT) AS "CLOSEDATE", 07 CASE WHEN MAX(B.LASTDT) > MAX(B.CLOSDT) 08 THEN MAX(B.LASTDT) 09 ELSE MAX(B.CLOSDT) 10 END AS "MAXDATE" 11 FROM ORDHST A LEFT OUTER JOIN ORDHDT B 12 ON A.ORDNBR = B.ORDNBR 13 AND A.ENTRDT = B.ENTRDT 14 GROUP BY A.ORDNBR,A.ENTRDT,A.CLOSDT) 15 WITH DATA

This works equally well in a SQLRPGLE or within CL's RUNSQL command.

Line 5 and 6: The MAX function returns the largest (maximum) value from the column (field) for the rows (records) selected.

Lines 7 – 10: I am using a CASE to give the largest of the two values. If you are unfamiliar with CASE I go into it in more detail here. The CASE statement, above, simply means if the largest value of the Last Activity Date is greater than the largest Line Closed date use the Last Activity date, else use the Line Closed date.

Lines 11 – 13: Is the join criteria. LEFT OUTER JOIN will include the row even if there is not matching record in the Order Detail History file. The values of the columns that would have come from the Order Detail History will be null.

Line 14: The GROUP BY is, as the name suggests, the way the records will be grouped, i.e. there will be one row for each Order Number and Enter Date. I had to include the Order Closed Date in the grouping so it would appear in the results.

Order Entry Order LASTDATE CLOSEDATE MAXDATE Number Date Completion Date 1 RVZ0710 1121008 8/01/13 112/06/28 112/06/29 112/06/29 2 TAK0940 1150824 5/29/15 115/05/13 115/05/12 115/05/13 3 R023000 1100515 1/06/09 10/901/06 109/01/06 109/01/06 4 R0PS891 1111217 1/03/13 111/09/15 111/09/15 111/09/15 5 S0CA842 1130708 5/17/13 113/03/18 113/03/30 113/03/30 6 P051690 1080510 5/08/05 108/02/02 108/02/01 108/02/02

I added the slashes ( / ) to make this more readable.

I can download this to my PC as a Microsoft Excel file and keep it for the next round of auditors.

 

Update Order History file

This is where is gets a bit difficult to follow what I want to do. Let me write it out so when you look at the SQL it will be a bit clearer what I am trying to achieve.

I want to update the Close Date (CLOSDT) in the Order History (ORDHDR) with the maximum value (MAXDATE) I made in the history file (HISTORY). But...

1. If the value of MAXDATE is zero (yes, there were a few records with neither Last Activity nor Line Close dates, I did not say this database was good) I want to leave the Order Close date unchanged.
2. If there was no Detail records to match the Order History then the value of MAXDATE will be null, then I want to leave the Order Header's CLOSDT unchanged.

After some playing I came up with the following:

01 UPDATE ORDHDR SET CLOSDT = 02 IFNULL((SELECT MAXDATE FROM QTEMP.HISTORY 03 WHERE HISTORY.ORDNO = ORDHDR.ORDNBR 04 AND HISTORY.ENTRDT = ORDHDR.ENTRDT 05 AND HISTORY.MAXDATE > 0),ORDHDR.CLOSDT)

This works fine if I use STRSQL. But it does not compile in a SQLRPGLE, and causes a run time error if placed in a RUNSQL statement in a CL program.

I was able to use it in a Query Management query, object type *QMQRY, or by using the Run SQL Statements command, RUNSQLSTM. I put mine in a QM Query.

Line 1: Update the column (field) CLOSDT in the table (file) ORDHDR.

Line 2 - 5: Ignore the IFNULL for now I will get back to that shortly. I have a "subselect" which selects the MAXDATE from the History file, where the Order Numbers (line 3) and Entry dates (line 4) match, but does not include any rows where the MAXDATE is zero (line 5). For records with a MAXDATE that is greater than zero the Order Close Date is updated with the value from MAXDATE.

MAXDATE will be null in two situations:

1. When the History file was made there was no Order Detail History rows/records to match the Order History row/record.
2. If MAXDATE is zero

This is where the IFNULL comes into play. If the value returned by the subselect is null then the value from the Order Close Date (CLOSDT) from the Order History (ORDHST) is used to update itself. In other words no change is made. More details on using the IFNULL can be found here.

My results are what I desired. The Order Completion date in the Order History has been changed to be the greater of the Last Activity and Line Close dates from the Order Detail History file, except when those dates are zero (line 3 below) or when null (not shown).

Order Entry Order History History History History Number Date Completion CLOSDT LASTDATE CLOSEDATE MAXDATE Date 1 RVZ0710 1121008 112/06/29 8/01/13 112/06/28 112/06/29 112/06/29 2 TAK0940 1150824 115/05/13 5/29/15 115/05/13 115/05/12 115/05/13 3 R035930 1090131 2/04/09 2/04/09 0 0 0 4 R023000 1100515 109/01/06 1/06/09 10/901/06 10/901/06 109/01/06 5 R0PS891 1111217 111/09/15 1/03/13 111/09/15 111/09/15 111/09/15 6 S0CA842 1130708 113/03/30 5/17/13 113/03/18 113/03/30 113/03/30 7 P051690 1080510 108/02/02 5/08/05 108/02/02 108/02/01 108/02/02

 

This article was written for IBM i 7.2, and should work for earlier releases too.