How to create a Subfile program without using any native I/O operations.

How to create a single page Subfile just using embedded SQL. 

Every RPGer knows about subfiles. So, in this post I am not going to describe you “what is a subfile” or “what are the three types of subfiles”.

One day a friend asked me “is it hard to create a single page subfile just using SQL and also with a search option?” He was though managing ‘Page up’ and ‘Page down’ would be difficult when using SQL. But it’s not. I have done it easily using a scrollable cursor. 

There are two main differences in my single page subfile program than many other single page subfiles on the internet. Those differences are,

1. There are no any native I/O operations, It’s just SQL.
2. There are no any numeric indicators.

Here I’m using simple search with one search criteria. But if we want to use more complex search criteria, then we might have to use dynamic SQL instead of Static SQL.

If anyone wants to re-use this program with a different file and a selection, then he/she has minimum changes to be done (Just change SQL selection and “dsp_fields” data structure). Following are the codes. Even you can download them from here.  

Step by Step guide to create SQL User Defined Functions(UDF) - How to create a SQL Function using RPGLE

How to convert long Julian to YYYYMMDD format using SQL 

In the system that we have in our office, all the date values are in long Julian format. When we are working with SQL it is a headache to convert those long Julian dates to YYYYMMDD numeric format. So I have decided to create two SQL functions. One is for convert 7-character long Julian date into YYYYMMDD numeric value and other one is for convert 7-Digits long Julian date into YYYYMMDD numeric value. In my previous post I showed how to create a SQL scalar function, this time I am going to create SQL scalar function and external scalar function both. 

First will see how to achieve above task using an external scalar function.

1^st Step

Let’s create RPGLE module.  This is a NOMAIN module. In that module I have created two procedures. One procedure accepts a 7-char parameter and returns 8-digits value and the other one accepts a 7-digits parameter and returns 8-digits value.

*-------------------------------------------------------------------       ctl-opt nomain;                                               *-------------------------------------------------------------------                                                                           /////////////////////////////////////////////////////////////       // Prototype definitions                                   //       /////////////////////////////////////////////////////////////       //Prototype for julToNum                                            dcl-pr julToNum zoned(8);                                             *n zoned(7) const;                                                end-pr;                                                                                                                                 //Prototype for jchToNum                                            dcl-pr jchToNum zoned(8);                                             *n char(7) const;                                                 end-pr;           /////////////////////////////////////////////////////////////       // This Procedure converts numeric long julian date        //       // into ISO numeric date.                                  //       /////////////////////////////////////////////////////////////       dcl-proc julToNum export;                                            //Prototype interface for incoming parms                           dcl-pi *n zoned(8);                                                  julDat zoned(7) const;                                           end-pi;                                                                                                                               monitor;                                                             return %dec(%char(%date(julDat:*longjul):*iso0):8:0);            on-error;                                                            return 0;                                                        endmon;                                                           end-proc julToNum;                                                  /////////////////////////////////////////////////////////////        // This Procedure converts character long julian date      //        // into ISO numeric date.                                  //        /////////////////////////////////////////////////////////////        dcl-proc jchToNum export;                                              //Prototype interface for incoming parms                             dcl-pi *n zoned(8);                                                    julDat char(7) const;                                              end-pi;                                                                                                                                    monitor;                                                               return %dec(%char(%date(%int(julDat):*longjul):*iso0):8:0);         on-error;                                                               return 0;                                                          endmon;                                                            end-proc jchToNum;

2^nd Step

Compile RPGLE module.

Step by Step guide to create SQL User Defined Functions(UDF) - How to concatenate multiple rows into a single row using SQL

How to concatenate multiple rows into a single row using SQL

To do this, there is GROUP_CONCAT function in MySQL. But unfortunately we do not have that function in DB2. So what we can do is create our own SQL function.  

One day my superior showed me a simple SQL query he executed. That query was listed all the claim types that each policy holder had. The result set was as below.

Plcy_Num	Plcy_Type
SSD0113	AA
SSD0113	MM
SSD1171	AA
SSD1171	MM
SSD1998	MM
SSD3084	SS
SSD5818	MM
SSD6722	MM
SSD7843	AA
SSD7843	MM

The he had executed below query.

select Plcy_Num, Plcy_Type   from  Library.claim_table     group by Plcy_Num, Plcy_Type     order by Plcy_Num, Plcy_Type

My superior asked me “Poorna, is there any way to get all the claim types into a single row against particular policy number?” I said “Yes. That should be possible. Give me few minutes I’ll try and let you know”

Then I googled and found that I can achieve this task by creating a simple SQL function.  

1^st Step

Type STRSQL command in the command line. (The only command that I type most number of times in a day)

2^nd Step

Let’s create the SQL function

First Post

I'm Poorna Sanjeewa. 26 years old (At the time of publishing this post) Software Engineer from Sri Lanka. I began my career as a trainee RPG programmer in 2012. From there to today  in my RPGLE career as a programmer, I have tried, I have done, I have learned and been learning so many things.

From today onward I will share what I have learned so far, whatever the things I am learning and things I am trying to do using RPGLE, SQL, JAVA and more. I hope this IBM i tech blog will be able add something new to your knowledge. Also please be kind enough to point out if I make any mistakes in my codings.

So that is it for the first post. In future there will be less talking and more coding in this blog.