This is probably a very specific issue but I’m quite proud of the solution. And it may prove to be useful to someone else (or me at some other point) so I’m putting it here for posterity.

The issue

We receive a list of sales transactions through an EDI interface. With the implementation of a new retail management system, these transactions now need to be pointed in the direction of the new application. This has proved interesting, on occasion, as the existing system is a lot more forgiving of incomplete data than the new one.

One of these cases, we have discovered, is that for some stores we’re not receiving transaction numbers. We have dates, times, store codes, transaction line numbers – but no transaction numbers. This means that if a store manages to enter two transactions at two tills at exactly the same time, we get a whole bunch of duplicate lines in the interface extract.

This interface is a transitional one (once the roll-out is completed for all stores, it will be redundant) so I shouldn’t be spending too much time on it. Just enough to make it work.

The Solution

Putting together an SQL statement that identifies transactions with duplicate line numbers is pretty easy, as is constructing a statement to update records based on a fixed criteria. The question is, how easy is it to construct an SQL statement to quickly update records based on a dynamic criteria.

The answer is: easier than I expected.

The Implementation

The (slightly simplified) code, is pasted below without explanation but if any part of it is unclear do feel free to either leave a comment or contact me and I will be happy to clarify.

     H
      *------------------------------------------------------------------------*
      * Program     :                                                          *
      * Description : Clean up duplicate lines                                 *
      * Written by  : Paul Pritchard                                           *
      * Date        :  2/07/2012                                               *
      *------------------------------------------------------------------------*
     D pDuplicate      DS                  QUALIFIED
     D  Date                         10A
     D  Time                          8A
     D  Store                         5S 0
     D  Register                      3S 0
     D  Transaction                   5S 0
     D  Line                          3S 0
     D*
     D SaveDate        S             10A
     D SaveTime        S              8A
     D SaveStore       S              5S 0
     D SaveReg         S              3S 0
     D SaveTxn         S              5S 0
     D SaveLine        S              3S 0
     D*
     D NewLine         S              3S 0
      *------------------------------------------------------------------------*
      /free

          // The Commitment control workaround
          exec sql Set Option Commit = *NONE;

          // Renumber any duplicate lines in the Sales File
          exsr srDuplicate;

          // And exit
          *INLR = *ON;

      /end-free
      *------------------------------------------------------------------------*
      * Subroutine: Renumber any duplicate lines in the Sales interface file   *
      *------------------------------------------------------------------------*
      /free

          begsr srDuplicate;

              // Initialise the work values
              exsr srReset;

              // Find the lines with duplicate numbers
              exec sql declare cSales cursor for
                  with
                      Duplication as
                      (select wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                              wrslstxn#, wrslslin#, count(*)
                       from SALES
                       group by wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                                wrslstxn#, wrslslin#
                       having count(*) > 1
                       order by wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                                wrslstxn#, wrslslin#)
                  select wrslsdate, wrslstime, wrslsistr, wrslsreg#, wrslstxn#,
                         wrslslin#
                  from SALES
                  where wrslsdate || wrslstime || char(wrslsistr) ||
                        char(wrslsreg#)
                  in (select wrslsdate || wrslstime || char(wrslsistr) ||
                      char(wrslsreg#) from Duplication)
                  order by wrslsdate, wrslstime, wrslsistr, wrslsreg#,
                           wrslstxn#, wrslslin#
                  for update of wrslslin#;
              exec sql open cSales;

              // Start retrieving the data
              dou SQLCOD = 0 and SQLCOD  100;
                      exsr srRenumber;
                      exec sql
                          update SALES
                          set WRSLSLIN# = :NewLine
                          where current of cSales;
                  endif;
              enddo;

              // Close the cursor and quit
              exec sql close cSales;

          endsr;

      /end-free
      *------------------------------------------------------------------------*
      * Subroutine: Renumber                                                   *
      *------------------------------------------------------------------------*
      /free

          begsr srRenumber;

              if pDuplicate.Date  SaveDate or
                 pDuplicate.Time  SaveTime or
                 pDuplicate.Store  SaveStore or
                 pDuplicate.Register  SaveReg or
                 pDuplicate.Transaction  SaveTxn;

                  SaveDate = pDuplicate.Date;
                  SaveTime = pDuplicate.Time;
                  SaveStore = pDuplicate.Store;
                  SaveReg = pDuplicate.Register;
                  SaveTxn = pDuplicate.Transaction;

                  NewLine = 0;
              endif;

              NewLine += 1;

          endsr;

      /end-free
      *------------------------------------------------------------------------*
      * Subroutine: Reset                                                      *
      *             Initialise the Save* Values                                *
      *------------------------------------------------------------------------*
      /free

          begsr srReset;

              SaveDate = *BLANKS;
              SaveTime = *BLANKS;
              SaveStore = *ZERO;
              SaveReg = *ZERO;
              SaveTxn = *ZERO;

          endsr;

      /end-free
      *------------------------------------------------------------------------*

I’m not going to criticise anyone for trying to move away from native I/O on the IBM i and into SQL, but I have seen a few eye-watering attempts. So in the spirit of sharing knowledge and making life a bit easier for all of us, here is an example of reading and updating a table using SQL.

Let’s start with the table we’re updating. It’s a very simple table and simply maps the SoldTo/ShipTo combination in the distibution system to a Store number in the retail system. You can build a simplified version of this using the following:

create table                                                   
testlib.sqltest
(index integer not null
 GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
 Soldto char (5) not null,
 shipto char (4) not null, 
 store numeric (5, 0) not null)

The requirement is that, based on various business rules, some SoldTo numbers in this table need to be changed. In order to keep things simple, the below program applies a simple rule of: If the store number is less than ten, change the SoldTo to 99999. (I know, I said I was keeping things simple).

The program, as it stands, is pretty much as simple as it gets, but there are couple of points that are worth noting explicitly.

Firstly, when we declare the cursor, we need to include a For Update clause to identify which field (or fields) can be updated.

Secondly, in the Update statement, the Where Current of C1 clause applies the update to all records returned by the cursor. That means that flexible mass updates with a minimum of logical mess are now at your fingertips.

     H
     D SqlRecord       DS
     D  SoldTo                        5A
     D  ShipTo                        4A
     D  Store                         5S 0
     D
     D NewSoldTo       S              5A
      /Free

         Exec Sql
            Declare C1 cursor for
               Select SoldTo, ShipTo, Store
               From SqlTest
               For Update of SoldTo;

         Exec Sql
            Open C1;

         Dou SQLSTATE>='02000';
            Exec Sql
               Fetch C1 into :SqlRecord;

            If SQLSTATE<'02000';
               If Store<=10;
                  NewSoldTo='99999';
                  Exec Sql
                     Update SqlTest
                     Set SoldTo = :NewSoldTo
                     Where current of C1;
               Endif;
            Endif;
         Enddo;

         Exec Sql
            Close C1;

         *INLR=*ON;

      /End-Free