RPG Edit Codes Example Table and Query

I've been referencing my "The Modern RPG Language" book for RPG edit codes for over 25 years now. Occassionally I use Google to search for "RPG Edit Codes" but since the book is right here, I just flip it open.

Then it occurred to me in this "everything can be solved with SQL" era of programming, that I could create a simple SQL table that contains the RPG edit codes, as well as provide the additional information that we always need to lookup.

The first thing I did was design the file/table so that it contains the Edit Code, a few edit code properties and 3 example values. Then I used the SQL INSERT statement to add the edit codes to the new table. Here's the table definition. Use RUNSQLSTM or RUNSQLF to run these statements:

CREATE TABLE EDITCODES (
  EC         CHAR(1)     NOT NULL DEFAULT ' ',
  COMMAS     DEC(3,0)    NOT NULL DEFAULT 0,
  DECPOINT   DEC(3,0)    NOT NULL DEFAULT 0,
  NEGSIGN    DEC(3,0)    NOT NULL DEFAULT 0,
  ZEROSUP    DEC(3,0)    NOT NULL DEFAULT 0,
  BLANKSZERO CHAR(1)     NOT NULL DEFAULT '',
  EXAMPLE1   VARCHAR(20) NOT NULL DEFAULT '',
  EXAMPLE2   VARCHAR(20) NOT NULL DEFAULT '',
  EXAMPLE3   VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(EC)
   );      

You could also decorate it up a bit, as I did, by running the following two additional statements:

LABEL ON TABLE EDITCODES
  IS 'Edit Code Table';

LABEL ON COLUMN EDITCODES
(
  EC         IS 'Edit                Code',
  COMMAS     IS 'Print               Commas',
  DECPOINT   IS 'Decimal             Point',
  NEGSIGN    IS 'Negative            Sign                Style',
  ZEROSUP    IS 'Zero                Supress',
  BLANKSZERO IS 'Blanks              or Zero             when Zero',
  EXAMPLE1   IS 'Examples 1          Packed(7,2)         1234.50',
  EXAMPLE2   IS 'Examples 2          Packed(7,2)         0.00',
  EXAMPLE3   IS 'Examples 3          Packed(7,2)         -1000.00'
   );  

The first LABEL ON TABLE statement adds object description 'text' to the file being created. The second LABEL ON COLUMN asssigns COLUMN HEADINGS (similar to COLHDG in DDS) to each field. These column headings are used by SQL atop the corresponding column when running the SELECT statement.

To insert the Edit Codes into the file, run the following INSERT statements. While doing them one at a time is fun, you may want to copy/paste them into a source member and use RUNSQLSTM on that source member to get them all done at once.

 INSERT INTO EDITCODES
   VALUES('1',1,1,0,1, '0', '1,234.50','.00','1,000.00');
 INSERT INTO EDITCODES
   VALUES('2',1,0,0,1, ' ', '1,234.50','   ','1,000.00');
 INSERT INTO EDITCODES
   VALUES('3',0,1,0,1, '0', '1234.50','.00','1000.00');
 INSERT INTO EDITCODES
   VALUES('4',0,0,0,1, ' ', '1234.50','   ','1000.00');

 INSERT INTO EDITCODES
   VALUES('A',1,1,3,1, '0', '1,234.50','.00','1,000.00CR');
 INSERT INTO EDITCODES
   VALUES('B',1,0,3,1, ' ', '1,234.50','   ','1,000.00CR');
 INSERT INTO EDITCODES
   VALUES('C',0,1,3,1, '0', '1234.50','.00','1000.00CR');
 INSERT INTO EDITCODES
   VALUES('D',0,0,3,1, ' ', '1234.50','   ','1000.00CR');

 INSERT INTO EDITCODES
   VALUES('J',1,1,2,1, '0', '1,234.50','.00','1,000.00-');
 INSERT INTO EDITCODES
   VALUES('K',1,0,2,1, ' ', '1,234.50','   ','1,000.00-');
 INSERT INTO EDITCODES
   VALUES('L',0,1,2,1, '0', '1234.50','.00','1000.00-');
 INSERT INTO EDITCODES
   VALUES('M',0,0,2,1, ' ', '1234.50','   ','1000.00-');

 INSERT INTO EDITCODES
   VALUES('N',1,1,1,1, '0', '1,234.50','.00','-1,000.00');
 INSERT INTO EDITCODES
   VALUES('O',1,0,1,1, ' ', '1,234.50','   ','-1,000.00');
 INSERT INTO EDITCODES
   VALUES('P',0,1,1,1, '0', '1234.50','.00','-1000.00');
 INSERT INTO EDITCODES
   VALUES('Q',0,0,1,1, ' ', '1234.50','   ','-1000.00');

 INSERT INTO EDITCODES
   VALUES('X',0,0,4,0, '0', '0123450','0000000','010000}');   

Now that the data is created, you need to query it. You can use STRSQL to do that, or using SQL iQuery's RUNiQRY command you can run the SQL SELECT statement interactively and display your edit codes along with some helpful examples. Here's the basic SELECT statement:

RUNIQRY SQL('SELECT * FROM EDITCODES')

or to print a list of them for safe-keeping:

RUNIQRY SQL('SELECT * FROM EDITCODES') OUTPUT(*PRINT)

The results, when viewed, would look like the image below:

This is a pretty good "right out of the box" listing, but the 1's and 0's in the various columns don't clearly tell you what you need to know. I prefer to write "reports" rather than "listings"; Listing are just that: A list of the data as with "SELECT * FROM...". A report customizing the output to the user's requirements, so I need to improve the results.  I often use the SQL SELECT statement along with the CASE/WHEN/THEN/ELSE clause as well as customized titles and column headings to provide reports. If I'm lucky, it makes it look like I wrote a custom program instead of just a query. Here's my version of the SELECT statement:

V 1001 050 Edit Codes Table                                                 
SELECT EC,                                                                  
       CASE WHEN COMMAS = 1 THEN 'Yes' Else ' ' end as                      
                  "Print               Commas",                             
       CASE WHEN DECPOINT=1 THEN 'Yes' Else ' ' end as                      
                  "Print               Decimal             Point",          
       CASE WHEN NEGSIGN = 0 THEN ' '                                       
            WHEN NEGSIGN = 1 THEN 'Left'                                   
            WHEN NEGSIGN = 2 THEN 'Right'                                   
            WHEN NEGSIGN = 3 THEN 'CR'                                      
            WHEN NEGSIGN = 4 THEN 'Embed' end  AS                           
                  "Negative            Sign",                               
       CASE WHEN ZEROSUP = 1 THEN 'Yes' else ' ' end as                     
                  "Zero                Supress",                            
       CASE WHEN BLANKSZERO = '0' THEN 'Yes' else ' ' end as                
                  "Print               Blanks              When Zero",      
       Example1,Example2,Example3                                           
 FROM EDITCODES 

Line 1 isn't strictly necessary but I like to include a title within the source member so that when I go back into it, I know what's being produced. SQL iQuery supports the legacy Query/400 "V 1001" control codes to allow users to embed optional Title text into their SQL SELECT statement source member. SQL iQuery reads the source member and interprets the "V 1001" control correctly and uses the text associated with it as the primary Title for the report. It also supports codes V 1002 and V 1003 for title lines 2 and 3 respectively.

To run the above SQL statement, issue the following CL command:

RUNSQLF SRCMBR(EDTCDE)

Where EDTCDE is the source member containing the above SQL statement and is stored in QSQLSRC on the library list. If you store it in another source file, include the SRCFILE parameter on the RUNIQRY command.

Here's what the new Report looks like. Compare it to the Listing from the plain SELECT above.

I like these results much better. The nice thing about Edit Codes is there are fewer than 20 of them that we care about, and that means they all fit nicely on one page/screen; no paging required.

A huge advantage of using SQL and SQL iQuery to create your listings and reports is that you can use its "write one and done" technology. This means you can now direct the output of your SELECT statement to the Display, Print, PDF, CSV, Excel, HTML, XML or JSON. The only thing that needs to be done to do that is alter the OUTPUT parameter's value. OUTPUT(*CSV), OUTPUT(*PDF) etc. No other changes required.

Caveat:

SQL iQuery supports custom column headings. Since IBM i DDS uses 20-byte column headings and supports up to 3 of them, SQL also supports these values, and always has. SQL iQuery inserts the column headings automatically above the individual fields similar to how STRSQL would do that. But sometimes you have derived fields, such as SUM(sales) or you may need to customize the headings when the developer who created the file didn't include any. SQL iQuery does a great job of handling custom column headings, and uses industry standard SQL to do it.

To create a custom column heading, layout the field being selected in the SQL statement and before the trailing comma that separates each field, insert the word "AS" and follow it with up to a 60-position Column Heading, enclosed in double quotes. Treat this 60-byte value as 3 individual Column Heading Lines; 20-bytes each; same as DDS COLHDG keywords. As mentioned, wrap the entire heading in double quotes (single quotes do not work) and do not count the quotes as part of the 60-characters. You may specify up to 60 characters but any number of characters up to 60 is supported. Every 20 positions, a new line is assumed. Thus, positions 1 to 20 are Column Heading Line 1, 21 to 40 are Line 2, and 41 to 60 are line 3. As you can see from the above examples, SQL iQuery shifts the column headings down so they are aligned properly. Thus if there are two column heading lines for one column but 3 for another, it will still look like a professional wrote the report or inquiry. It also right-justifies numeric column headings so they line-up with thier corresponding numeric column data. Here's an example:

SELECT CUSNUM "Customer            Number", BALDUE "Balance             Due" FROM QIWS/QCUSTCDT

This sample file QCUSTCDT is shipped with PDM and iis included in the QIWS library on your system. This SELECT statement provides nicely labeled column headings for the Customer Number and Balance Due fields. The challenge in getting the column headings spaced correctly isn't too difficult, but it does require a little playing with it. That's why I prefer to keep my SQL statements in source file members. That way I can use the SEU Ruler or the RDi cursor position to determing if I've got the 20-character spacing correctly specified. This is one great reason to make sure you have good Column Headings for your data.

The Web You Say?

What a great resource to be able to get to our EDITCODE table and view it on the Web without the need to write server-side code. SQL iQuery for Web/Mobile provides access to your SQL statements. Normally object authority applies to being able to get to the source file members that contain the SQL statements so security questions are answered.

See the results below courtesy of SQL iQuery for Web/Mobile.