
I am sure I am not the only person when I get a number returned in my SQL results I have to look at it carefully to determine is that number millions, billions, trillions, or even bigger?
SELECT SUPPORTED_VALUE FROM QSYS2.SQL_SIZING WHERE SIZING_ID = 18303 SUPPORTED_VALUE --------------------- 18446744073709551600 |
For an explanation of what the SQL View SQL_SIZING shows see here.
Those of us who have been programming for any time in IBM i have formatted numbers in DDS files using edit codes and edit words in the source:
AAN01N02N03T.Name++++++RLen++TDpBLinPosFunctions+++++++++++++++++++++++++ A R SCREEN A FIRST 20Y 0O 3 2EDTCDE(J) A SECOND 20Y 0O 4 2EDTWRD(' - - - - 0 ') 18,446,744,073,709,551,600 1844-6744-0737-0955-1600 |
Using an Edit word, see field SECOND, I can use other characters to format the number, not necessarily into a recognizable format. Whatever I do to these fields they remain numbers.
In modern RPG I have two Built in Functions that allow me to edit numbers. Alas, I have to output to a character type variable.
01 **free 02 dcl-s First char(30) ; 03 dcl-s Second char(30) ; 04 dcl-c BigNbr const(18446744073709551600) ; 05 First = %editc(BigNbr:'J') ; 06 Second = %editw(BigNbr: ' - - - - 0 ') ; 07 dsply First ; 08 dsply Second ; 09 *inlr = *on ; |
Line 1: I did say this example was going to be modern RPG, there are no columns in modern RPG.
Lines 2 and 3: Definitions for the variables that will contain the output from the BiFs.
Line 4: Definition of a constant that contains the big number we have been using in these examples.
Line 5: Format the number using the Edit Code BiF, %EDITC, using edit code "J".
Line 6: Format the number using the Edit Word BiF, %EDITW, using the same format as I did in the display file. I have wrapped this line to second line so that it will fit on this page.
Lines 7 and 8: Use the Display operation code, DSPLY, to show the contents of the two variables.
When the two DSPLY are executed I see:
DSPLY 18,446,744,073,709,551,600 DSPLY 1844-6744-0737-0955-1600 |
What can I do with SQL?
Alas, there is no edit code or edit word functionality in Db2 for i like there is in DDS or modern RPG. There are a couple of SQL functions I can use to format a number with comma separators:
SELECT SUPPORTED_VALUE AS "Original number", VARCHAR_FORMAT(SUPPORTED_VALUE,'999,999,999,999,999,999,999') AS "VARCHAR_FORMAT", TO_CHAR(SUPPORTED_VALUE,'999G999G999G999G999G999G999') AS "TO_CHAR" FROM QSYS2.SQL_SIZING WHERE SIZING_ID = 18303 Original number VARCHAR_FORMAT TO_CHAR -------------------- -------------------------- -------------------------- 18446744073709551600 18,446,744,073,709,551,600 18,446,744,073,709,551,600 |
I have used two SQL functions in this statement: VARCHAR_FORMAT and TO_CHAR. They do the same things, I just prefer to use VARCHAR_FORMAT. In the VARCHAR_FORMAT function I gave commas ( , ) as the thousand separators. As I am in the USA we use the comma as the separator. With the TO_CHAR I have used "G" as the separator character, which results in the separator character being the default thousand separator character.
If I substitute the commas or "G" with another character I get the following error:
SELECT VARCHAR_FORMAT(SUPPORTED_VALUE,'9999-9999-9999-9999-9999') FROM QSYS2.SQL_SIZING WHERE SIZING_ID = 18303 SQL State: 22018 Vendor Code: -20476 Message: [SQ20476] Format string for function VARCHAR_FORMAT not valid. Cause . . . . . : The format string 9999-9999-9999-9999-9999 specified for function VARCHAR_FORMAT is not valid. |
But I can put commas in those places instead using the "G":
SELECT VARCHAR_FORMAT(SUPPORTED_VALUE,'9999G9999G9999G9999G9999') FROM QSYS2.SQL_SIZING WHERE SIZING_ID = 18303 00001 ------------------------- 1844,6744,0737,0955,1600 |
There are other things I can do with numbers and the VARCHAR_FORMAT:
SELECT VARCHAR_FORMAT(1234.56,'999G999G999D999') AS "1", VARCHAR_FORMAT(1234.56,'000G000G000D000') AS "2", VARCHAR_FORMAT(-1234.56,'999G999G999D999') AS "3", VARCHAR_FORMAT(-1234.56,'999G999G999D999MI') AS "4", VARCHAR_FORMAT(1234.56,'S999G999G999D999') AS "5", VARCHAR_FORMAT(-1234.56,'S999G999G999D999') AS "6", VARCHAR_FORMAT(1234.56,'L999G999G999D999') AS "7", TO_CHAR(-1234.56,'L999G999G999D999') AS "8" FROM SYSIBM.SYSDUMMY1 |
Rather than display the results in columns I am going to show them in rows so that they will fit in the width here:
1 = 1,234.560 2 = 000,001,234.560 3 = -1,234.560 4 = 1,234.560- 5 = + 1,234.560 6 = - 1,234.560 7 = $ 1,234.560 8 = $ -1,234.560 |
The first thing to notice is that all of the results, except number 2, have leading spaces. I could have changed the length of the formatting string to match the size of the value. But if this was a real life scenario I would need my results in the results column to align to the right, whether the number of digits is 6 or 14:
SELECT VARCHAR_FORMAT(NUMBER,'999G999G999G999D999MI') AS "Formatted number" FROM MYLIB.TESTFILE Formatted number -------------------- 1,234.560 12,345,678,912.000 1,234.560- |
Back to my column of results…
Result 2: This has leading zeroes as I replaced the 9s in the formatting string with 0s. Therefore, the leading zeroes are preserved.
Result 3: A negative value without any special formatting has the minus sign as the leading character.
Result 4: If I want a minus sign at the end of the string I must use MI at the end of the formatting string.
Result 5: The S character inserts the sign character at the start of the formatted string, regardless of the number of characters. Here a positive number results in a plus sign at the start of the string.
Result 6: Same formatting as result 5, but with a negative number has a minus sign at the start of the result.
Result 7: The L character is used to denote the default currency symbol. Here it is a dollar sign ( $ ). This is shown at the start of the result.
Result 8: Same as result 7, but for a negative amount. As I did not give a code for the where the minus sign is to display this is the same happens as it did with result 3. I was sneaky here using the TO_CHAR rather than the VARCHAR_FORMAT.
If I needed to remove the leading spaces from any of the results I could use the Left Trim function, LTRIM, like this.
SELECT LTRIM(VARCHAR_FORMAT(1234.56,'999G999G999D999')) AS "1", LTRIM(VARCHAR_FORMAT(-1234.56,'999G999G999D999')) AS "3", LTRIM(VARCHAR_FORMAT(-1234.56,'999G999G999D999MI')) AS "4" FROM SYSIBM.SYSDUMMY1 1 = 1,234.560 3 = -1,234.560 4 = 1,234.560- |
Now I can use the VARCHAR_FORMAT in my results and see if that number is really trillions.
- SQL function VARCHAR_FORMAT
- SQL function TO_CHAR
This article was written for IBM i 7.4, and should work for IBM i 7.3 TR5 and 7.2 TR9 and later.
No comments:
Post a Comment
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.