Make SQL Edit the Way You Want It To
December 15, 2010 Ted Holt
|
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:
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 Stuff I Didn’t Publish This Year
|
Error when compiling. The RETURN from EditDec (after the endmon) needs to return something? I’m not an RPG person.
change
endmon;
return;
to
endmon;
return ‘ ‘;
.