• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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:

    • 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:

    Separators

    Zero Balances

    No Sign

    CR

    Parentheses

    Trailing Minus

    Leading Minus

    Yes

    Yes

    1

    A

    E

    J

    N

    Yes

    No

    2

    B

    F

    K

    O

    No

    Yes

    3

    C

    G

    L

    P

    No

    No

    4

    D

    H

    M

    Q

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Meet digital age demands while maximizing your IT investment.

    Future-proof your mission-critical applications with Rocket® Solutions for IBM® i that keep your business ahead of the curve.

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    VAULT400:  Which is right for you? Online back-up, DR, HA Webinar. Jan. 20
    Townsend Security:  FREE Podcast! Key management best practices: What new PCI regulations say
    Help/Systems:  Robot/SCHEDULE Enterprise, the job scheduling and server management tool

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Attachmate Updates Reflection Suites Oracle’s Withdrawal of JDE ‘Blue Stack’ Raises Questions

    2 thoughts on “Make SQL Edit the Way You Want It To”

    • rbeethe says:
      August 16, 2017 at 6:33 pm

      Error when compiling. The RETURN from EditDec (after the endmon) needs to return something? I’m not an RPG person.

      Reply
    • drew says:
      August 24, 2018 at 12:37 pm

      change
      endmon;
      return;
      to
      endmon;
      return ‘ ‘;
      .

      Reply

    Leave a Reply Cancel reply

Volume 10, Number 38 -- December 15, 2010
THIS ISSUE SPONSORED BY:

SEQUEL Software
neuObjects
inFORM Decisions

Table of Contents

  • Make SQL Edit the Way You Want It To
  • End of Year Miscellanea
  • Discovering Which Locked Object is Holding Up Your Job

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle