Make SQL Edit the Way You Want It To

Note: The code accompanying this article is available for download here.

In the last issue of the year, I like to do something fun. In 2008, I found my way through mazes. In 2007, I solved the peg game and talked about some other unusual things. This year I’ve done something fun that I think you’ll find to be a little more practical.

We use SQL a lot in my shop for ad-hoc queries. One annoyance is that the various and sundry SQL clients we use don’t display the numbers the way we want to see them. For example, sometimes I want commas to separate thousands and sometimes I don’t. The SQL clients don’t seem to be able to read my mind to determine my preferences.

Awhile back I got the idea to write an SQL function that would format numbers using the edit codes IBM i professionals use in DDS and RPG. I finally took some time recently to write the function, which I call EDITDEC (Edit Decimal), and today I share it with you.

First, you need an RPG service program. You’ll find the source code for the EDITDEC module in the downloadable code for this article. The source code has the instructions you’ll need in order to install the function.

Once EDITDEC is safely residing in the library of your choice, you’ll be wanting to use it in SQL queries. Here’s how it works.

EDITDEC has four parameters:

• A decimal value with no more than 63 total digits and no more than 20 decimal places.
• The number of digits in the returned value.
• The number of decimal positions in the returned value.
• An edit code.

The second and third parameters do not have to match the definition of the first parameter, but normally you’d want them to.

You may choose from 16 IBM standard edit codes, four edit codes of my creation, or leave the edit blank for default behavior.

The 16 standard edit codes are 1-4, A-D, J-M and N-Q. I defined E-H to show negative numbers in parentheses. I did not program support for X, Y or Z. The following table summarizes the edit codes:

You may use either uppercase or lowercase letters.

The default edit, specified by a blank edit code, is defined in the last element of the compile-time array. I copied the settings for edit code L (ell), but you can define the default how ever you want.

EDITDEC returns the number as a character string with the requested edit applied. If the decimal input is invalid, EDITDEC function returns three asterisks.

Here’s an example.

select number, editdec(Number, 13, 2, 'd')
  from testdata

Running this statement in green-screen SQL gives me the following:

           NUMBER   EDITDEC
12,345,678,901.23-  12345678901.23CR
    12,345,678.90-     12345678.90CR
           123.45-          123.45CR
              .00
           123.45           123.45
    12,345,678.90      12345678.90
12,345,678,901.23   12345678901.23

One thing you’ll quickly run into is that SQL builds a broad output column because it allows for 63 digits. I have been using the substring function to trim it to a decent length, like this:

select substr(editdec(Number, 13, 2, '1'),1,19) as No_Sign,
       substr(editdec(Number, 13, 2, 'a'),1,19) as Trailing_CR,
       substr(editdec(Number, 13, 2, 'e'),1,19) as Parentheses,
       substr(editdec(Number, 13, 2, 'n'),1,19) as Leading_Minus
  from testdata

 NO_SIGN              TRAILING_CR          PARENTHESES          LEADING_MINUS
12,345,678,901.23    12,345,678,901.23CR  (12,345,678,901.23)  -12,345,678,901.23
    12,345,678.90        12,345,678.90CR      (12,345,678.90)      -12,345,678.90
           123.45               123.45CR             (123.45)             -123.45
              .00                  .00                   .00                  .00
           123.45               123.45                123.45               123.45
    12,345,678.90        12,345,678.90         12,345,678.90        12,345,678.90
12,345,678,901.23    12,345,678,901.23     12,345,678,901.23    12,345,678,901.23

Between EDITDEC and FMTDATE, you should be able to make numbers look the way you want them to.

What new SQL function can you invent to improve everyday life? I hope to hear from you.

RELATED STORIES

Formatting Dates with SQL

Two A-maze-ing Programs

Stuff I Didn’t Publish This Year

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot