Converting the current date to a number in SQL

This is handy if you find yourself needing to populate a numeric timestamp field (eight digit number, yyyymmdd) with the current date.

update TheFile                                                
set TheDate = Dec(Replace(Char(current date, ISO), '-', ''), 8, 0)
where TheSelection

Obviously, TheFile is the file you are populating, TheDate is the field and TheSelection is whatever selection criteria you happen to be using.

Flexible SQL with Query Manager on the IBM i

Back in November, I mentioned that it was possible to extract the SQL source from existing queries using the RTVQMQRY command. I have since had an opportunity to play around with the IBM i Query Manager and have found it to be a surprisingly useful reporting tool.

What is it?

According to the iQuery Management Programming pdf, Query Management is the i5/OS implementation of query management Common Programming Interface (CPI). This allows you to retrieve data from a relational database and – most usefully – control the output formatting. It does this, unsurprisingly, by splitting the data extraction (the QM Query) and presentation (the QM form).

Where is it?

Broadly speaking, there are two options you need to know about in order to use Query Manager. First there is the STRQM command which will lead you through the process of creating and maintaining Query Management queries and forms. And then there are the Query Management CL Commands, all of which can be found on menu CMDQM, and which allow you to work with and execute existing QM Queries.

Inevitably, there are multiple ways of achieving the same result. What follows is what works for me but I do recommend that, if you do want to play around with this, investigate the available options to see what works best in your environment.

Before you begin

In STRQM, take the Work with Query Manager profiles option and change the following:

  • The Default library for QM objects is initially set to *CURLIB. This is less than optimal, so I changed this to my own Development/Test library.
  • Paging down, I also found the Default query creation mode. This is initially set to Prompted (which gives you a rather painful Query/400-like interface for creating queries). I changed this to SQL.

Define your Query

There are a couple of options here. The first, and simplest, approach is to STRQM, take the Work with Query Manager queries option and then the Create option. This will give you an SEU screen into which you can enter your SQL (assuming, of course, your default query creation mode is set to SQL). The advantage of taking this approach is that this screen includes both a syntax checker and an option to execute the statement (either all, or a sample). So you can enter, validate and test your SQL, all from the same screen.

If, however, you have an unshakable aversion to SEU, you can create and edit a source member using whatever tool or tools you prefer and then use the CRTQMQRY command to create your query. Once the query is created, your source member is no longer needed.

Whichever way you do it, you will eventually end up with an object of type *QMQRY. This is your QM Query, an SQL select that can be executed with the STRQMQRY command.

And some flexibility

This is where things start to become interesting. The STRQMQRY command includes a Set variables (SETVAR) parameter which allows you to specify up to 50 variables to be set by query management before the query is run. That’s parameters in non-IBM speak. This is easier to explain with an example, so here’s an example using a couple of realy simple purchase order tables. The code for creating these tables and populating them with a couple of one-line orders is below the fold.

And so, to the query. I have, a very simple QM Query (which I have imaginatively named QMTEST) that lists all orders in the simple orders database.

The query looks like this…

select header_order_number, header_order_date,    
       line_ean_number, line_quantity, line_price 
from orderhdr                                     
join orderline on header_index = line_header_index

And the interactive result looks like this…

         HEADER_ORDER_NUMBER  HEADER_ORDER_DATE  LINE_EAN_NUMBER  LINE_QUANTITY  LINE_PRICE
         -------------------  -----------------  ---------------  -------------  ----------
000001   TST0000001           01/21/2013         1234567890123               5     27.5000 
000002   TST0000002           01/22/2013         1234567890321               4     16.5000 
******  * * * * *  E N D  O F  D A T A  * * * * *                                          

This is fine, as far as it goes, but it’s unlikely that you will ever want a list of all orders ever entered. So here’s the same QM Query with a date selection added.

select header_order_number, header_order_date,       
       line_ean_number, line_quantity, line_price    
from orderhdr                                        
join orderline on header_index = line_header_index   
where header_order_date between &FROMDATE and &TODATE

Now, if I run the query interactively, it will prompt for a from and to date. More usefully, however, I can enter a from and to date when launching the query with: STRQMQRY so that:

STRQMQRY QMQRY(QMTEST) SETVAR((FROMDATE '''01/21/2013''') (TODATE '''01/21/2013'''))

Returns this:

         HEADER_ORDER_NUMBER  HEADER_ORDER_DATE  LINE_EAN_NUMBER  LINE_QUANTITY  LINE_PRICE
         -------------------  -----------------  ---------------  -------------  ----------
000001   TST0000001           01/21/2013         1234567890123               5     27.5000 
******  * * * * *  E N D  O F  D A T A  * * * * *                                          

There are a couple of gotchas to watch out for here.

  • Because lower-case characters in variable names are changed to upper-case characters when passed to the command processing program, everything needs to be upper case.
  • If you are passing a string (or date) surrounded by quotes, you need to use triple quotes. The ouer quotes are removed and the double quotes within the value are condensed to a single quote when the value is passed to the command processing program. No, it didn’t make much sense to me either.

    You can, of course, pass a variable rather than a constant value and as soon as you do this, it really does become useful.

    Design your Form

    Now the fun begins. Having a parameterised, executable SQL object has its uses, but the QM form provides a presentation layer that allows you to format the output into something that’s almost pretty enough to put in front of your end users.

    You can edit a source member and then use the CRTQMFORM command to create your QM form, but the source is very sparse indeed. As such, I found that using the Work with Query Manager report forms option in STRQM to go through the prompts was quite useful.

    Execute anywhere

    And then you’re done. QM Queries are not a universal solution, obviously, but I have seen cases where they can be useful – most notably in conjunction with a tool such as CoolSpools that will email the QM Query, as an Excel workbook, to whatever distribution list has asked for it.

    And finally

    This started out as a really short blog post in which my only intention was to make a few notes to help me keep track of what I’m doing. As can sometimes happen, however, it grew into this monster that you have just ploughed your way through.

    Continue reading “Flexible SQL with Query Manager on the IBM i”

Killing queries with SQL

Back in the 20th Century, Query was a handy tool for… well, writing ad-hoc queries. The form-filling interface is remarkably simple – it really can be used by anyone – and rather inflexible. Now, of course, we have SQL – which is a lot more powerful – and a collection of third-party tools to help with the building your query.

But Queries exist. Some of them are on menus. They must be destroyed!

Here’s an approach:

RTVQMQRY QMQRY(querylib/queryname) SRCFILE(sqllib/QSQLSRC) ALWQRYDFN(*ONLY)

The Retrieve Query Management Query (RTVQMQRY) command allows you to retrieve Structured Query Language (SQL) source from a query management query (QMQRY) object. The source records are placed into an editable source file member.

But you don’t have to have a QMQRY object to use this command. ALWQRYDFN allows you to specify whether query information is taken from a query definition (QRYDFN) object when a query management (QMQRY) object cannot be found. There are three possibilities:

  • *NO: Never use the QRYDFN. If the QMQRY object doesn’t exist, the command will fail
  • *YES: If the QMQRY object doesn’t exist, use the QRYDFN
  • *ONLY: Ignore the QMQRY object and just use the QRYDFN

Once the command is executed, you will have a member (called queryname) in library/member sqllib/QSQLSRC, based on query querylib/queryname.

And now your options are endless.

Getting an ISO date from a 6 digit number – The lazy approach

Someone asked me today if I had an SQL function to convert a six digit number into an ISO date. I don’t because if you store dates in date fields then there is no need to convert the date back into a date.

But we have a legacy table. And this table stores a date as a six digit number which represents YYMMDD. I’m not going to write a function for this, but the approach is pretty simple, and shown below:

with ISODATE as
 (select STORENO as I_STORENO,
 char(20000000 + STOREOPEN) as I_STOREOPEN
 from STORES)
select STORENO, 
       STORENAME,  
       date(substr(I_STOREOPEN, 1, 4) || '-' || 
            substr(I_STOREOPEN, 5, 2) || '-' || 
            substr(I_STOREOPEN, 7, 2)) 
from STORES 
join ISODATE on STORENO = I_STORENO 

In order to improve the clarity a bit, I have broken it up a bit:

The with clause builds a temporary table of store numbers and nicely formatted opening dates. This converts the six digit date into an eight character string which is almost good enough to pass to the build in date function.

The main select then splits the date into its component parts and then concatenates these (with a ‘-‘ separator) so that the date can handle it.

For example:

  • A STOREOPEN date of 1st February 2003 would be represented in the STORES table as 30201
  • The with clause builds table ISODATE in which I_STORENO is represented as 20030201
  • The substr and concatenation functions generate a character representation of 2003-02-01 and then passes this to the date function

And now you have a value on which normal date calculations can be performed.

Of course, the better approach would be to replace these legacy tables in order to make this sort of manipulation unnecessary.

Unduplicating duplicate lines with SQL and RPG

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
      *------------------------------------------------------------------------*

Converting ISO Dates to Text Representations in SQL

This is more for my own benefit than for anyone else, but I can see this question cropping up a few more times over the next few months. The issue is that I have a table containing an ISO date and have to populate a legacy table in which the date is represented as a ten character string (YYYY-MM-DD).

This works:

select trim(char(year(iso_date))) || '-' ||
       substr(digits(month(iso_date)), 9, 2) || '-' ||
       substr(digits(char(day(iso_date))), 31, 2)
from ...

The digits function returns a fixed-length character-string representation of the absolute value of a number. then all you need to do is identify the bit of the string that you need to use.

Using SQL Fetch/Update in RPG

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                             

Nuts to the command line: Using the SQuirreL SQL client with an IBM i

Splash I use SQL a lot. Not only embedded within my RPG programs but also interactively to check and maintain data. The IBM i does provide a green-screen interactive SQL session but as soon as you start writing anything more than simple select and update statements, this becomes very cumbersome. The System i Navigator also provides an SQL tool which, while an improvement on the green screen session is still pretty basic.

It is possible, of course, to write your SQL in a source code editor like Notepad++ and then paste it into the System i Navigator SQL tool. Obviously, though, this is not ideal so I was rather pleased to discover that there is a better approach.

I heard about SQuirreL SQL earlier this week and, having finally gotten around to installing it, I have to say that I am very impressed. SQuirreL SQL is open-source SQL client that uses JDBC to connect to pretty much any database you can think of. It’s fast and feature-packed and really is a boon to developers.

In order to get it up and running, you need to first download JTOpen, the open-source version of the IBM Toolbox for Java, and then download and install the SQuirreL Client itself. MC Press Online has a useful walk-through of the steps you need to take to get this all working.

Once you’re in, though, it really is a lovely tool. I am not going to attempt to write a review on the basis of three hours of tinkering, but one thing that has struck me is that the keyboard shortcuts are well worth learning.

Beyond that, I have yet to encounter a task that I can’t complete faster with SQuirreL than with my previous approach.