Pages

Wednesday, June 8, 2016

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

update column with the greater value from one 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.

3 comments:

  1. Here is a minor improvement to your UPDATE statement, to only update the rows that are changing. If table ORDHDR is being journaled, this improvement will run a lot faster in the presence of a large volume of rows, because only rows being changed are updated:

    UPDATE ORDHDR SET CLOSDT =
    (SELECT MAXDATE FROM QTEMP.HISTORY
    WHERE HISTORY.ORDNO = ORDHDR.ORDNBR
    AND HISTORY.ENTRDT = ORDHDR.ENTRDT
    AND HISTORY.MAXDATE > 0)
    WHERE EXISTS( SELECT 1 FROM QTEMP.HISTORY
    WHERE HISTORY.ORDNO = ORDHDR.ORDNBR
    AND HISTORY.ENTRDT = ORDHDR.ENTRDT
    AND HISTORY.MAXDATE > 0)

    ReplyDelete
  2. the where exists is a good idea if you do not expect to fix a majority of the rows, but the overhead of checking whether to update can eat up your journaling time savings if there are few records not updated.

    ReplyDelete
  3. Could you have done this with a MERGE statement?

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.