31
Thu, Oct
2 New Articles

Pirouette Like a Pro with Virtual Pivot Tables

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

It was another typical Monday. Three users had changed their passwords on Friday, two toner cartridges needed changing, and there was one request for advice on how to buy a PC. Then Fred called from promotions. “I need a report that shows sales quantity by product, by month, for the last 12 fiscal months,” he said. “You know, a separate line for each item, with sales totals across the page.”

“OK,” I replied, “I can do that.” “I might need to select only certain product types,” he continued, “and sometimes I might only want items with sales under a certain level, and I might want to look at only a single customer or a single sales territory, so can you make it really flexible? Oh, yeah, and I might want to insert the data into a word processing document. Will that be done today?

It’s really important, and I need it right after my golf game.”

“Sure thing, Fred,” I managed to respond, as I wondered if that coffee spray would hurt my monitor.

“Have a nice day,” he crooned. “Gotta go.” Fred thinks the programmer lives in the cabinet under the console.

I collected my wits for a few moments and then got down to business. I decided to give Fred a spreadsheet. That would be flexible enough. He could control when the data gets refreshed. He could filter, sort, and subtotal it any way he wanted, and he could include it in a word-processing document. The raw data would be in the order detail table. But wait a minute, he wants monthly totals by item, with a separate column for each month. That’s a cross-tab. The spreadsheet just got a lot more complicated.

The whirring and clanking of the creative process filled the air, punctuated by the acrid smell of burning coffee from the overheated, empty pot. Self-preservation is a powerful motivator.

To fulfill all of Fred’s criteria, I decided I needed to build an SQL query with a virtual pivot table, which would keep the spreadsheet simple and cut down on network traffic.

“Whoa!” you say. “What’s a virtual pivot table?” Well, here’s the executive overview.

Transactional data is generally stored in a normalized database. A row usually contains an identifying key, such as a document number, and fields that describe the


transaction, such as date, quantity, related revenue and cost information, or maybe even a journal reference and fiscal period. Each row relates to only one transaction.

It’s often easier to interpret the data when a cross-tab format is used to display it. It’s called a cross-tab because related data spreads across, instead of down, the page. A sample cross-tab row might contain an item number and quantity or revenue information by month for each month in the fiscal year. A virtual pivot table denormalizes the data by making it appear as if there is an array of buckets in the database. It’s called a pivot table because (you guessed it) it pivots the orientation of related data from vertical to horizontal.

OK, so back to the case at hand. I know I need figures summarized by item, and I might want to break that apart by customer or sales territory, so my level of granularity will be item within customer within sales territory. If Fred doesn’t care about the customer or sales territory, he can handle that in his spreadsheet. So far, so good. I know that SQL has a SUM function, but how can I get that to add things up by fiscal month?

 

CASE to the Rescue

 

IBM calls the answer a computer-aided systems engineering (CASE) statement. I prefer to think of it as a function because, like many SQL statements, it returns a value. This is an extremely powerful characteristic because, as you shall see, it means that CASE statements can be nested inside other functions.

To begin with, I can use a CASE statement for each month to assign the current quantity (QTYSAL) to the correct monthly column. Each row of raw data will match the criteria of exactly one of the CASE statements. The rows that don’t match will each receive a value of zero.

CASE FSCMON WHEN 1 THEN QTYSAL ELSE 0 END, -- Month 1
CASE FSCMON WHEN 2 THEN QTYSAL ELSE 0 END, -- Month 2
...

CASE FSCMON WHEN 12 THEN QTYSAL ELSE 0 END -- Month 12

If I were using a column of the date data type instead of a numeric month field, I would use the MONTH( ) function to extract the month.

Now I can combine CASE with SUM to get the conditional summarization I need. The total of all QTYSAL values from rows where FSCMON = 1 will be retrieved in one result value when I specify the following:

SUM(CASE FSCMON WHEN 1 THEN QTYSAL ELSE 0 END), -- Month 1

I should probably also specify the output format, since the default column size is really only useful if you’re a quantum cosmologist. I’d like a 15-digit packed-decimal value, with four digits after the decimal point, please:

DECIMAL(SUM(CASE FSCMON WHEN 1
THEN QTYSAL ELSE 0 END),15,4), -- Month 1

Now, there’s just one more detail. Remembering that a little programming paranoia is a healthy thing, if I decide to use my view in a left outer join at some point, I should make sure that zero is returned for items that have no transactions. I could do this with a second CASE statement, but I prefer the older and more compact IFNULL or COALESCE functions. Actually, I like IFNULL best because the name tells me what it does. COALESCE sounds vaguely like something that must be requested in triplicate and approved by committee. Both functions will return the first value that is not null.

When I add IFNULL to my construct, it looks like this:

DECIMAL(IFNULL(SUM(CASE FSCMON WHEN 1


THEN QTYSAL ELSE 0 END),0),15,4), -- Month 1

 

Creating the View Source

 

SQL does not compile. I can, however, store a single SQL command in a source member and execute it with the Run Structured Query Language Statement (RUNSQLSTM) command. I end up with something roughly analogous to a traditional compile, except that, with RUNSQLSTM, I can do many other things, too. If you don’t have the SQL licensed program product, don’t worry. There are other options. I’ll cover that later.

Take a look at Figure 1. It’s a typical script. Think of it as the source for the view. In section A, I declare my intentions. I’m going to create a view, which is really just an arrival sequence logical file. I’m going to call it FREDSVIEW and put it in library VIEWLIB. I like to use system naming conventions, but you can use the International Organization for Standardization (ISO) convention of name.collection if you prefer. RUNSQLSTM will accept either.

In section B, I list the view’s column names. These would be the alias names in DDS, and they’re what ol’ Fred is going to see, so they’re fairly verbose. Note that the list is enclosed in parentheses.

I get to the SELECT clause in section C. It identifies the columns and columnar functions that will provide the data. There must be exactly one column listed in the SELECT clause for every entry in the column name list. This is where I put the cross-tab incantations over which I’ve just finished laboring so diligently.

I tell SQL which tables contain the data in the FROM clause in section D. In this case, everything is in the INVCDETL table, but it’s usually necessary to code a few joins here.

And finally, in section E, I have the GROUP BY clause. It defines the control breaks that SQL will use when summarizing the data. Here, I am required to list all of the data elements from the SELECT clause that are not being summarized in some way. This means all of the columns except the monthly total values. Sometimes this list gets quite long and seems like a very inefficient use of system resources. Since I always try to write efficient code, I might be tempted to fool SQL by using a MAX or MIN function on columns that aren’t really group control values. I would pay a heavy penalty in response degradation and CPU usage if I did that. SQL has no sense of humor.

I’ve saved my source member as FREDSVIEW, and all I need to do now is to run the script.

 

RUNSQLSTM? Ernie Should Have Copyrighted It

 

I’m not talking about the freeware utility that MC published back in the Dark Ages, although it will work if you don’t have SQL on your AS/400. You might also create a Query Management Query (CRTQMQRY) from your source and run that with the STRQMQRY command, or you could use the EXCSQLSTM command that appeared in the December 1994 issue of MC (see “The EXCSQLSTM Utility”). If you’d prefer to keep the source on your PC or a network drive, you could also use the query script editor in Operations Navigator (right click on Database and select Run SQL Scripts).

I’m talking about the true-blue RUNSQLSTM command. The options you select when you run it will often have an impact on how your SQL object behaves. For instance, when you create a stored procedure, you need to specify the date format and user profile that will be used at runtime. I don’t care about that right now, but if I selected the correct combination of options, I could standardize and use it for everything. Take a look at Figure
2. Obviously, I need to tell RUNSQLSTM the name of the source file (PETESLIB/MYSQLSRC) and source member (FREDSVIEW). I always use commitment control in stored procedures, so I specify COMMIT(*CHG), which is also the default. I like *SYS naming (LIBRARY/FILE) and *ISO dates (yyyy-mm-dd). I want to *RUN the script as opposed to just checking it for syntactic correctness. I want the system to allow


the copying of data if necessary (*OPTIMIZE), and I’d like the default user profile (this only applies to stored procedures) to be the requester. If you’re lazy like I am, you might want to create a PDM user option to make things really easy in the future. Start PDM (STRPDM) and select option 9, Work with user-defined options, press F6 = Create, type in a convenient code, and enter the command. You can even use the command prompter by typing RUNSQLSTM and pressing F4 = Prompt.

So, without further ado, give it the old smoke test.

 

The Object Emerged as the Mists Slowly Cleared

 

RUNSQLSTM produces a nice diagnostic report for you, so if the command doesn’t execute successfully for some reason, it’s not too difficult to tell why. Mismatched view column and select column entries and missing or mismatched punctuation are common errors. This time, we did everything correctly the first time. Fred can stop worrying. You know how that throws off his golf game.

Just to be sure everything was OK, I ran a couple of quick queries. First, I got a list of all of the SKATEKEY items that were sold to customer 113242 in 1999 and pressed the print key (Figure 3). Then I requested the same list from FREDSVIEW. SQL returned one row, which I have pasted on several lines because you can’t see it when I scroll my monitor to the right (Figure 4). Notice that the raw data contains no transaction for month 7 and two transactions for month 8. The view has zeros in QTY_SOLD_MONTH_07 and has added both of the month 8 rows into QTY_SOLD_MONTH_08. All I need now is a simple spreadsheet, and I’ll be home before the WWF gets started. Isn’t it incredible that we can get paid for having this much fun?

 

A Few Tips and Gotchas

 

Virtual pivot tables require system processing overhead. They are very efficient because they require only one pass through the data, but the values that your pivot table returns are not stored. They’re calculated at runtime.

After you build any new view, turn on debug and query it interactively a few times with some common WHERE clauses. Look at the job log and see if the query optimizer has asked you to build any indexes. Having the correct indexes can make a big difference in performance. A poorly performing query can make your whole system sluggish.

You can use mixed-case and even embedded blanks in your object names, and they can be up to 128 characters in length. The AS/400 handles this just fine, as do some—but not all—of the third-party products that access your AS/400 data with ODBC. Microsoft Query is inconsistent. Sometimes it allows mixed-case names enclosed in quotes, and other times it doesn’t. Seagate Software’s Crystal Reports handles mixed-case names just fine but has trouble with embedded blanks. Seagate’s online analytical processing (OLAP) tool doesn’t work with mixed-case, quotes, or embedded blanks. Mixed-case can greatly enhance readability, but if you want to be absolutely safe, stick to uppercase 10-character names.

 

REFERENCES AND RELATED MATERIALS

 

• Optimizing Transact-SQL: Advanced Programming Techniques. David Rozenshtein, Eugene Birger, and Anatoly Abramovich. Fremont, California: SQL Forum Press, 1995

• The Essence of SQL: A Guide to Learning Most of SQL in the Least Amount of Time. David Rozenshtein Ph.D. Fremont, California: SQL Forum Press, 1998


CREATE VIEW VIEWLIB/FREDSVIEW
(ITEM_ID,
TERRITORY,

CUSTOMER_ID,
FISCAL_YEAR,
QTY_SOLD_MONTH_01,
QTY_SOLD_MONTH_02,
...

QTY_SOLD_MONTH_12)

AS SELECT
ITMNBR,
SLSTRT,

CUSTNO,
FSCYR,
DECIMAL(IFNULL(SUM(CASE FSCMON WHEN 1
THEN QTYSAL ELSE 0 END),0),15,4), -- Month 1
DECIMAL(IFNULL(SUM(CASE FSCMON WHEN 2
THEN QTYSAL ELSE 0 END),0),15,4), -- Month 2
...

DECIMAL(IFNULL(SUM(CASE FSCMON WHEN 12
THEN QTYSAL ELSE 0 END),0),15,4) -- Month 12

FROM INVCDETL

GROUP BY
ITMNBR,

SLSTRT,
CUSTNO,
FSCYR

A D

E

B

C

Figure 1: Once you know how, constructing the source is easy.

RUNSQLSTM SRCFILE(PETESLIB/MYSQLSRC)

SRCMBR(FREDSVIEW)

COMMIT(*CHG)

NAMING(*SYS)

DATFMT(*ISO)

DATSEP(-)

PROCESS(*RUN)

ALWCPYDTA(*OPTIMIZE)

USRPRF(*USER)

DYNUSRPRF(*USER)

Figure 2: Create the view with RUNSQLSTM.

INVNBR INVLIN ITMNBR QTYSAL UPRICE CUSTNO SLSTRT FSCYR FSCMON SLSJRN

55,121 1 SKATEKEY 500.0000 17.4320 113,242 115 1,999 1 INV62949 56,424 1 SKATEKEY 295.0000 17.4320 113,242 115 1,999 2 INV63841 57,292 1 SKATEKEY 621.0000 17.4320 113,242 115 1,999 3 INV68121 58,104 1 SKATEKEY 550.0000 17.4320 113,242 115 1,999 4 INV69101 59,317 1 SKATEKEY 319.0000 17.4320 113,242 115 1,999 5 INV69914 59,821 1 SKATEKEY 604.0000 17.4320 113,242 115 1,999 6 INV70153 61,002 1 SKATEKEY 515.0000 17.4320 113,242 115 1,999 8 INV76929 62,814 1 SKATEKEY 522.0000 17.4320 113,242 115 1,999 8 INV77484 63,181 1 SKATEKEY 504.0000 17.4320 113,242 115 1,999 9 INV79617 64,209 1 SKATEKEY 485.0000 17.4320 113,242 115 1,999 10 INV80112 65,315 1 SKATEKEY 546.0000 17.4320 113,242 115 1,999 11 INV91115 66,444 1 SKATEKEY 509.0000 17.4320 113,242 115 1,999 12 INV93114 select * from invcdetl where itmnbr='SKATEKEY' and custno = 113242 and fscyr = 1999 order by invnbr

Figure 3: List the test data set.

ITEM_ID TERRITORY CUSTOMER_ID FISCAL_YEAR

SKATEKEY 115 113,242 1,999 QTY_SOLD_MONTH_01 QTY_SOLD_MONTH_02 QTY_SOLD_MONTH_03 500.0000 295.0000 621.0000 QTY_SOLD_MONTH_04 QTY_SOLD_MONTH_05 QTY_SOLD_MONTH_06

550.0000 319.0000 604.0000 QTY_SOLD_MONTH_07 QTY_SOLD_MONTH_08 QTY_SOLD_MONTH_09 .0000 1,037.0000 504.0000 QTY_SOLD_MONTH_10 QTY_SOLD_MONTH_11 QTY_SOLD_MONTH_12

485.0000 546.0000 509.0000

select * from fredsview where item_id =’SKATEKEY’ and customer_id = 113242 and fiscal_year = 1999

Figure 4: Compare the view with the raw data.


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: