11
Wed, Dec
4 New Articles

Practical RPG: Creating Work Files with SQL

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

Did you know that you can create work files in SQL very easily? This article shows you how.

In my previous article, I identified a class of applications in which SQL and RPG join forces through the magic of work files. These applications use SQL to access the data but still take advantage of ILE RPG to perform more in-depth database manipulation. The trick is creating the work file, and this article will show two separate ways to do that. Note that this article isn't specific to the work file application I described; you can use either of these techniques any time you need a work file, even just a temporary ad hoc work file for analysis or extract purposes.

The Business Requirement

Here's the business requirement: give me a list of all customers who have one or more orders in the past three months with lines underpriced by more than 10 percent. You could theoretically do this with a single massive SQL statement, but a lot depends on how difficult it is to determine the expected price. When all you have is a single standard price per item, it's relatively easy:

select distinct OHCUST                        

from ORDHDR join ORDDTL on ODORNO = OHORNO

             join ITMMST on IMITEM = ODITEM  

where ODITPR < (IMITPR * .90);              
order by OHCUST;              

There you have it, a list of customer numbers in customer number order that have at least one customer order line where the order price was less than 90 percent of the item's standard price. Although the field names might be relatively self-explanatory to those who read RPG, let me explain them a little more fully. There are three files:

File Name

Description

Prefix

ORDHDR

Order Headerone record per order

OH

ORDDTL

Order Detailone record per invoice line

OD

ITMMST

Item Masterone record per item

IM

If you're old-school RPG like me, you might recognize the basic database design approach. Each file has a six-character name, a description, and a two-character prefix. Every field in those files has a six-character name, the first two characters of which are the file's prefix. That leaves four characters to define the field, and we try to keep those consistent across files. Thus xxORNO is order number in every file, where xx is the file's prefix.

The order header identifies the customer order and has one record per order. This record identifies, among other things, the order number and the customer number. The order detail contains one record for every order on a line, including the item number and the unit price. Finally, the item master has one record for every item and is where we store the item price.

The SQL statement above would create the list of customers that have orders in which a line was underpaid by more than 10 percent. Pretty simple, but also not particularly useful. Think about what will probably happen: as soon as you give that to the users, they're going to want a list of the orders. Not only that, they're going to want a list of the offending order lines. And for context, I find that users typically end up wanting a list of all the lines for all the orders in which any line has one or more underpaid lines. And don't be surprised if they want it sorted by customer and order number. That's actually not too difficult (for now):

with UnderpaidOrders as (select ODORNO      

from ORDDTL join ITMMST on IMITEM = ODITEM            

where ODITPR < (IMITPR * .90))            

select * from ORDDTL where ODORNO in        

(select ODORNO from UnderpaidOrders)              

order by OHCUST, ODORNO;                  

This query uses a Common Table Expression (CTE) to create a temporary list named UnderpaidOrders of order numbers that have at least one line where the unit price is less than 90 percent of standard. We then select all the fields from every record in ORDDTL where the order number is in that list, and order it by customer and order number. This gives me a list of all the lines in every order where at least one line is underpriced.

Technique 1: Externally Described Files

This technique is probably the most familiar to RPG programmers. We start with an existing externally described file, which we then load with the SQL statement. In this case, we're selecting all the fields from the ORDDTL order detail file, using SELECT * FROM ORDDTL. This means we simply need a file with the same layout as ORDDTL. It's not hard to create a file in QTEMP using CRTDUPOBJ (with the caveat that you have to get a lock on the object; for production files that traditionally means having a template file with the same layout). Run the SQL statement to load the file and then process the selected records using the technique we learned in the previous article:

     dcl-f ORDDTL extfile('QTEMP/ORDDTL');

The ORDER BY clause in the SELECT statement ensures that the records are placed in the file in proper order and your program can just process the file with a traditional read. If you wanted to go really old-school, you could even use the RPG cycle and level breaks; how's that for merging the old world and the new? For those of you who are cringing at my bringing up the cycle, let's move on to the more up-to-date processing style.

Technique 2: The All-SQL Solution

To do this, we need to take advantage of embedded SQL. At that point, we're no longer beholden to file layouts and we use a bit more sophistication. Let's assume, for example, that we only want a few specific fields from the order detail file. In addition, we'd like to include some header information in our query as well. We could of course go out and chain for the header data as we need it, but there are design conditions under which you might want that data replicated in the result file. For example, the query we're writing might print allow the user to select either a traditional RPG printer file report or alternatively export to another format such as XML or CSV. By creating the work file with all the needed fields, the export is a very simple step.

Whatever your reasoning, there may be times when you simply don't have that predefined externally described work file. In that case, we can go to a full SQL solution. These lines will be included in your embedded RPG program, so remember that they will be preceded by the EXEC SQL opcode. For example, our first task is to delete any existing work file already in QTEMP. Use the DROP statement:

exec sql drop table QTEMP.Underpaid;

That's all it takes; the table UNDERPAID in QTEMP will be dropped. You might notice that I used dot (.) syntax rather than slash (/) syntax. This has been available since 7.1, and I highly recommend it; it means you can drop the same SQL statement into any available SQL client, whether it's IBM i Navigator or SQuirreL SQL. I also deleted the table named UNDERPAID. In a moment, I'll create that same table with data.

OK, on to the next case. Let me expand the SQL statement to get the order header information and also to create the temporary file. It's really not hard.

exec sql                                      

create table QTEMP.Underpaid as            

(with UnderpaidOrders as (select distinct ODORNO    

   from ORDDTL join ITMMST on IMITEM = ODITEM            

   where ODITPR < (IMITPR * .90))            

select OHORNO, OHCUST, OHORDT,              

         ODORLN, ODITEM, ODITPR, ODORQT, IMITPR

from ORDDTL join OHCUST on ODORNO = OHORNO

             join ITMMST on IMITEM = ODITEM            

   where ODORNO in (select OHORNO from UnderpaidOrders)              

   order by OHCUST, ODORNO))
with data;                

Some of the statement is familiar; the CTE stays the same, creating a list of all order numbers having at least one underpaid line. Then we have two significant differences. The first is that the query selects specific fields, and it gets those fields from all three files (ORDHDR, ORDDTL, and ITMMST). It still uses the CTE to select only the records it wants, but the fields are very specific. The real work, though, is in the CREATE TABLE statement. The syntax is very simple, but it's extraordinarily powerful: this statement creates a table named UNDERPAID in QSYS that contains the selected fields, and fills it with the rows that meet my selection criteria (in this case, being part of an order with at least one underpaid line).

One important point: since the name is 10 characters or less, it will be created with that as its QSYS name and as its SQL name. If instead I used the table name UnderpaidOrders, I'd end up with a QSYS name of UNDER00001. Since this is a work file intended only to be used in SQL, that might not be an issue for you, but don't be surprised if you see that odd name if you execute a DSPLIB.

Now technically this is an externally described file like any other, and RPG programs could be compiled over it. However, the file would have to exist before you could compile the program, and any time you changed the field list you'd have to change that file: all the stuff you're used to dealing with today. If instead, though, you simply used embedded SQL and a cursor, you could simply read through the newly created file. Another article will detail that technique. For now, though, you can easily use this to create any work files you might need.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

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: