Wed, Jul
0 New Articles

Embedded SQL and Dynamic Sorting Subfiles

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

When sorting data to be displayed in a subfile, the Open Query File (OPNQRYF) command is often used. This comes in handy when you are providing the user with the ability to change how he displays the data in the subfile. I agree that OPNQRYF is a powerful way to select and sort data before loading your subfile, but its real strength is sorting and selecting data in a batch environment. I have used it with subfile programs, and, although
it’s a bit of a pain, it does work, especially if you use optimizing techniques. Another method for providing multiple sorting criteria in your subfiles is to use logical files. One problem with logical files is that you are required to maintain an access path for every sort criterion required by your users. Another problem is the complexity of your subfile load routine when using multiple logical files to provide the users with different looks at the data. That gets ugly.

What if you had a tool that allowed you the same select and sort capabilities as logical files and OPNQRYF but did so in a flexible and efficient manner, while allowing you to produce a well-structured, easily maintainable program? Wouldn’t that be awesome? Well, with embedded SQL, that’s just what you get. SQL embedded into your RPG program allows you to sort and select the data as you want, but it also provides the user with some added power.

What if multiple users from different departments are going to use your subfile program but, as is usually the case with users, they each want to see the data in a different order? You can certainly accomplish this by including enough logical files in your program to cover every possible sorting criterion, but this may require many logical files, especially if the user wants secondary and tertiary sorts. This may also create unnecessary access path administration for the system if all the logical files are created for this one application. It also makes for an ugly subfile build routine in your RPG program. Another method to allow dynamically sorting subfiles is to use OPNQRYF. You could create one open query for each possible sort or dynamically create the open query based on what the user wants to do. Either way, you would be passing parameters back and forth between the RPG and CL programs to either build the OPNQRYF command or select which specific OPNQRYF command to use. I’ve messed around with the latter method, and the CL can get cumbersome if you try to give the users complete flexibility by allowing them to sort by more than one field at a time. I love users, but that’s a lot of work just to provide them with a different view of the same data.

By embedding SQL, you can dynamically build your SELECT statement based on how the user wants to sort the data, all in one neat little subroutine. You can also use techniques that allow you to provide this capability very efficiently and, with a couple of minor changes, for data on other AS/400s. I’ll tell you how it’s done.

There’s Nothing to See Here, Folks

The DDS in Figure 1 is a standard load-all subfile. I have coded a window record format to allow the user to select fields by which to sort. I could have made this window a subfile window, but there are only four fields from which to choose, so I decided to make the selection window a regular format.

Look, Mom. No F-specs

Well, that’s not exactly true. In Figure 2 (page 96), there is one F-spec in the RPG program, but it is used for the display file. There are no F-specs for the data files; I will introduce them in the embedded SQL. The program is a basic load-all subfile program to list the names from a database file. This program allows the user to display a window by using the F4 key and selecting a field by which to sort. When the user presses the Enter key after selecting a field, the data is sorted by that field and redisplayed on the screen. Cool, huh? Take a look at the code, and I will explain what the embedded SQL is doing.

Behind Option 14

Before I dig into the RPG code itself, I want to discuss the compile options you need to make this work. First of all, this source member is type SQLRPGLE, not RPGLE. The SQL tells the compiler that there are embedded SQL statements in this RPG code. The command used to create a program using an SQLRPGLE-type source member is Create SQL RPG ILE Program (CRTSQLRPGI). This command verifies, validates, and, if you choose, prepares the embedded SQL statements during compile time. To use this command, you must have the SQL Development Kit licensed program.

When using the CRTSQLRPGI command, there are some compile options you need to take into consideration. You may want to tinker with some of the parameters when compiling SQLRPGLE source code. The Relational database (RDB) parameter tells you which database you are using. It can be the name of your local DB2 database, or, if you are networked using Distributed Relational Database Architecture (DRDA), it can contain the name of a remote AS/400 database. If you use a remote database in the RDB parameter, you need to tell the compiler where to create the SQL package. This is done by entering a library and package name in the SQLPKG parameter. The Delay preparation (DLYPRP) parameter asks you if you want to delay preparation of your SQL statement until the program is run. Answer *YES to this parameter because you do not want to perform redundant access path validation. If you enter *NO in this parameter, access path validation is performed at compile time and again when the cursor is opened later during runtime. By entering *YES, the validation is done only at runtime.

There are many other parameters in the CRTSQLRPGI command. I have mentioned a few here that I commonly deal with. However, I suggest you peruse them for yourself to see if there is anything of interest to your specific implementation of this technique.

The D-specs

Now that you know how to compile an RPG program containing embedded SQL, you would probably like to know how to code one. First, you create the basis for your dynamic selection capability. This is done with the help of D-specs. Define a 500-byte standalone field, SELCT1, to hold your initial SQL statement. (The number 500 was selected at random. You need a variable large enough to hold your SQL statement.) Initialize this field with your SQL statement. Define a second 500-byte standalone field, SELCT2, to hold the user-defined SQL statement, which will be made up of the initial SQL statement from

SELCT1 plus the selection criteria assigned by the user. Also define a standalone field called ORDER and initialize it to DBLNAM, which is the last-name field in the database file. The ORDER variable will be used to append the field selected by the user to the ORDER BY clause in the SELCT1 variable.

Rules and Regulations

All blocks of SQL code must begin with a slash (/) in position 7 followed by the EXEC SQL statement and must end with a slash in position 7 followed by the END-EXEC command. In between EXEC and END-EXEC is where you place all of your SQL code. Each line is signified by a plus sign (+) in position 7. The CONNECT RESET statement connects to the local DB2 database. If you were going to connect to a database on another machine, you would do so by replacing the RESET parameter with the name of the remote database.

Prepare, Declare, and Open

Now take a look at the rest of the mainline routine. First, you execute the PREP subroutine; this is where the dynamic SQL statement will be built. Create the dynamic SQL statement from what the user selected. Because this is the first time through, use the default value for the ORDER variable, setting it to DBLNAM in the D-specs. On subsequent times through this subroutine, the user will have selected a field by which to sort, and that data will be placed in the ORDER variable. Once you have appended the data in ORDER to the original SELECT statement defined in your D-specs, you have a complete SELECT statement that will retrieve your data and order it appropriately.

Now use that SELECT statement and retrieve your data. Then use the PREPARE statement to prepare a statement called SEL, using the new SELECT2 variable you just created. The PREPARE statement will validate the SQL statement contained in the SELECT2 variable. Next, declare a cursor called MYCSR by using the DECLARE statement. You should allow this cursor the ability to scroll; create it from the SEL statement you just prepared in the previous line. Finally, open the cursor by using the OPEN command. Clear as spaghetti code, right?

OK. Before I go on, let me explain cursors a little. When SQL runs a SELECT statement, the resulting rows create a result table. A cursor provides a way to access that result table. It’s kind of like a subfile in its own right. The cursor is used within an SQL program to maintain a position in the result table. SQL uses the cursor to work with the data in the result table and make it available to your program. Your program may contain several cursors, although each must have a unique name.

There are two types of cursors: serial and scrollable. A serial cursor is defined without using the SCROLL keyword. This type of cursor allows you to fetch each row once—and only once. If you want to retrieve a row more than one time, you must close the cursor and reopen it. This kind of cursor is perfect for a load-all subfile because you want to see the data only once before loading it into your subfile. The subfile provides the scrolling of the data in this case.

The scrollable cursor is what you have defined in your program, even though it is a load-all subfile. The advantage of a scrollable cursor is that you can move back and forth throughout the rows of data. By using different parameters in a FETCH statement, you can read the next or prior row, navigate to the first or last row, read the same row over again, or position any number of rows forward or backward from the current row. This type of cursor can be used if you are building your subfile one page at a time. The data remains in the cursor and is loaded into your subfile only one page at a time.

OK. You have successfully validated the SELECT statement contained in your variable by using the PREPARE command; you’ve created a scrollable cursor that will contain the result table from your SELECT statement by using the DECLARE command;

and you’ve opened the cursor by using the OPEN command. Now you are ready to load the subfile from the cursor.

Here, Boy—Fetch!

Check out the SFLBLD routine. Instead of loading the subfile directly from the database file, load it from the cursor by using the SQL FETCH command. Notice that the FETCH command is inside your DO loop. Use the FETCH NEXT command and place the results in your display file field names. Because this is a scrollable cursor, you should use the NEXT parameter, but, as I mentioned before, you don’t have to use a scrollable cursor when using a load-all subfile. I had you do this simply because I wanted to show you some of the parameters you can use with a scrollable cursor. Figure 3 shows that, in addition to NEXT, there are other parameters with the FETCH command.

Use SQLCOD to determine whether or not to write to the subfile record format. If you are astute—and I know you are—you may have noticed that SQLCOD is not defined anywhere in the program or the display file. So where did it come from? Well, when you embed SQL in your program and use the CRTSQLRPGI command to create the program, a data structure not unlike the file information data structure is included in your program. It is filled with all sorts of information on the SQL statements embedded inside your program. One example of that information is the error code returned when an SQL statement is run. It is contained in the SQLCOD variable and is set to 0 upon successful execution of an SQL statement. For more information on the SQL Data Area (SQLDA), look in IBM’s DB2 for AS/400 SQL Programming manual. For this program, the only data you should use from the SQLDA is the SQLCOD variable.

Once your subfile is loaded, you are ready to display it. Now go back to the mainline.

Let’s Kick It Up a Few Notches

When displaying the subfile the first time, the data is sorted by last name because you initialized the ORDER variable to the last-name field. Now that the user has the data in his control, he can press F4 to sort the data another way. If F4 is selected, a number of subroutines are executed. First, the SORT subroutine is executed, which determines what to place in the ORDER variable. Then, the CLEAN subroutine is executed. This simply closes the cursor, MYCSR. Once the ORDER variable is given its proper value and the cursor is closed, the PREP and SFLBLD routines are executed again. You have already seen what they do.

The last thing I want to take a look at is the SORT subroutine. There is no new, exciting subfile code in this routine, nor is there any SQL. However, there is some code that may interest you. It is here that you determine what field to append to the ORDER BY clause in your dynamic SQL statement. A window is displayed that lists the fields contained in the subfile. The user can select one of these fields and press Enter. The program then determines which field was selected and places that field in the ORDER variable. Pretty simple, huh? Well, if you really want to separate this technique from what’s easily implemented by using logical files and open query files, you can allow the user to select more than one field. If the user wants to sort by first name, last name, and nickname, you allow him to enter a 1 next to first name, a 2 next to last name, and a 3 next to nickname. Then, place logic in your SORT routine to interrogate all the fields selected, place them in the correct order, and place that information in the ORDER variable. So instead of the ORDER variable containing one field to append to the ORDER BY clause, it now contains three.

Now that the user has selected a field to sort by and pressed the Enter key, the subfile is redisplayed in the appropriate order, with no open query files and no access paths to


maintain. You have now seen how to use embedded SQL and subfiles to select and sort data dynamically. If you are interested in learning more about SQL programming, I suggest you dig into IBM’s DB2 for AS/400 SQL Programming manual. There you will learn all sorts of advanced techniques, such as allocating storage and loading the SQL data area (SQLDA) by using the SQL DESCRIBE command as well as implementing optimizing techniques to make your programs scream even more. Combining embedded SQL and subfile programming allows you to take your applications to new heights. Enjoy.


• DB2 for AS/400 SQL Programming (SC41-5611-03, CD-ROM QB3AQ803)

A DSPSIZ(24 80 *DS3)


A CA03 CA12 CF04









A SFLSIZ(0050) SFLPAG(0017)







A 4 2'Last Name' DSPATR(HI)

A 4 26'First Name' DSPATR(HI)


A 4 55'Nick Name' DSPATR(HI)

A 1 2'SFL013RG'

A 1 26'Dynamic Sort with Embedded SQL'



A 2 71TIME



A 23 2'F3=Exit' COLOR(BLU)

A +3'F4=Prompt' COLOR(BLU)

A +3'F12=Cancel' COLOR(BLU)


A WINDOW(*DFT 11 25)

A TAB1 1 B 3 2

A 3 5'Last Name'

A TAB2 1 B 4 2

A 4 5'First Name'

A TAB3 1 B 5 2

A 5 5'Middle Initial'

A TAB4 1 B 6 2

A 6 5'Nick Name'

A 10 1'F3=Exit' COLOR(BLU)

A 10 11'F12=Cancel' COLOR(BLU)

A 1 3'Select a sort field'


Figure 1: Using SQL to load a subfile requires no special DDS.


* To compile:





Fsfl013df2 cf e workstn sfile(sfl1:rrn1)

F infds(info)


* Information data structure to hold attention indicator byte.

Dinfo ds

D cfkey 369 369

* Constants for attention indicator byte

Dexit C const(X'33')

Dprompt C const(X'34')

Dcancel C const(X'3C')

Denter C const(X'F1')

Dorder S 8 INZ('dblnam')

Dselct1 S 500A INZ('SELECT dblnam, dbfnam,D dbmini, dbnnamD FROM sfl001pfD ORDER BY ')

Dselct2 S 500A INZ(' ')

* Establish the connection to the remote machine. The -842 return

* code indicates that the connection is already established. If

* you want to connect to the local machine, use CONNECT.




C exsr prep

C exsr sflbld


C dou (cfkey = exit)

C write fkey1

C exfmt sf1ctl

C select

* prompt to selection sorting criteria

C when cfkey = prompt

C exsr sort

C exsr clean

C exsr prep

C exsr sflbld

C when cfkey = cancel

C leave

C endsl

C enddo


C exsr clean

* Disconnect the current connection.




C eval *inlr = *on


* Prepare SQL cursor


C prep begsr


* Prepare the SQL statement for validation, since the program was

* compiled with DLYPRP (*YES), it will wait until it is used before

* it prepares the cursor.


C eval selct2 = %trimr(selct1) + ' ' + order



C+ PREPARE sel FROM :selct2



* Declare the SQL cursor to hold the data retrieved from the SELECT





* Open the SQL cursor.





C endsr


* Clean up before exiting


C clean begsr


* Close the SQL cursor after all processing is complete.





C endsr


* Build the subfile


C sflbld begsr


* Clear the subfile

C eval *in31 = *on

C write sf1ctl

C eval *in31 = *off

C eval rrn1 = 0


* Process the records in the SQL cursor until the return not = 0

C dou sqlcod 0

* Get the next row from the SQL cursor.



C+ INTO :dblnam, :dbfnam, :dbmini, :dbnnam


C if sqlcod = 0

C eval rrn1 = rrn1 + 1

C write sfl1

C endif

C enddo


C if rrn1 = 0

C eval *in32 = *on

C else

C eval rrn1 = 1

C endif


C eval *in90 = *on


C endsr


* SORT - prompt to select sort criteria


C sort begsr


C exfmt window1


C select

C when tab1 *blank

C movel(p) 'dblnam' order

C clear tab1

C when tab2 *blank

C movel(p) 'dbfnam' order

C clear tab2

C when tab3 *blank

C movel(p) 'dbmini' order

C clear tab3

C when tab4 *blank

C movel(p) 'dbnnam' order

C clear tab4

C endsl


C endsr

Figure 2: Embedded SQL makes powerful RPG.

NEXT PRIOR FIRST LAST BEFORE AFTER CURRENT RELATIVE--+-host-variable-+--integer Figure 3: Additional parameters for scrollable cursors allow easy access to a database.



Support MC Press Online


Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: