• Give Me Fewer (Not More!) Parameters, Please!

  August 2, 2016 Ted Holt

  Today’s musings fall into the “why would anybody want to do that?” category. “Why would anybody want to be president?” asks Barack Obama. “Why would anybody steal a groundhog?” asks Rita in Groundhog Day. My question is, “Why would anybody pass 16,382 parameters to a subprocedure?” There is a better way. I stand second to none in my admiration for parameters. The first system I learned to program, the IBM System/3 Model 12, allowed no parameter passing to RPG programs or OCL procedures. Cloning diminished–and life improved–when I started working on a S/34, which allowed OCL procedures to receive

  …

  Read more

• Using Lateral Correlation To Define Expressions In DB2 For i

  August 2, 2016 Michael Sansoterra

  The SQL implementation in DB2 for i is second to none. However, one irritating thing common to various SQL dialects is the need to repeat expressions in query. As SQL matured over the years, techniques such as nested table and common table expressions became available to, among other things, reduce repetitive expressions. This tip illustrates the use of the LATERAL correlation as another way to avoid repetition. The Problem Say you’re writing a report for a grocery wholesaler, where markup on food items is small and discounts are even smaller. Looking for orders that were not priced correctly, you’re tasked

  …

  Read more

• A Second Look At SQL Descriptors

  July 19, 2016 Paul Tuohy

  Note: The code accompanying this article is available for download here. My previous article, A First Look at SQL Descriptors, looked at how SQL descriptors can be used in constructing and processing dynamic SQL statements. This article examines how SQL descriptors can be used in processing the information returned through a dynamic SQL statement. As an example, we will write a program that dynamically constructs the select clause in an SQL select statement. Traditionally, this would cause us a problem in our RPG programs since we would not be able to code the INTO clause correctly. We have

  …

  Read more

• Side-By-Side Lists

  July 19, 2016 Hey, Ted

  An end user has asked me to provide him a spreadsheet with two independent lists, one beside the other. Can I use SQL to satisfy his request? –Dennis Yes, you can. Dennis works for a manufacturer, and his data had to do with routing operations. I’m going to use a more common type of data to illustrate. Let’s say we work for a company that sells dohickeys, thingamajigs, and whatchamacallits. We offer these items in various colors, but we don’t offer all items in all colors.

  In my last article, I promised to tell you about four new OLAP aggregate functions: FIRST_VALUE, LAST_VALUE, nTH_VALUE and RATIO_TO_REPORT. These valuable functions can reference data from other rows in a query result set relative to the current row. This tip covers the fourth function in the list, RATIO_TO_REPORT. The RATIO_TO_REPORT Function The new RATIO_TO_REPORT function is heaven sent, as it makes it easy for developers to avoid writing an UGLY arithmetic expression to do a simple calculation common in business. RATIO_TO_REPORT simply divides a numeric column or expression value in the current row by a SUM aggregate of

  …

  Read more

• A Generic Character Editing Routine

  July 12, 2016 Jon Paris

  Note: The code accompanying this article is available for download here. Recently I was asked if I knew of a way to edit character strings. For example, take a character string representing a product code such as “AX12345Q” and edit it to produce “AX-123-45 Q”. My initial reaction was to reach for an edit word, but sadly they only work for numerics, for some strange reason. I set about building a subprocedure that offered the necessary flexibility in insert characters and also added a few “defenses” against mismatched parameters. Before describing the code, let’s look at the prototype for

  …

  Read more

• Formatting Dates With SQL, Take 3

  July 12, 2016 Ted Holt

  This story contains code, which you can download here.   I love it when someone improves something I’ve produced. In this case, that someone was Barry Arnold, an A-1COBOL programmer with whom I had the privilege to work once upon a time. Barry improved my FMTDATE SQL function for use in his shop. Maybe his enhancement will help you, too. Barry’s shop, like many others, must deal with data from many different sources, and that data is often formatted in ways that are not conducive to RPG and COBOL programming. Barry found himself having to de-edit character dates before

  …

  Read more

• New OLAP Aggregate Functions In DB2 for i, Part 1

  July 12, 2016 Michael Sansoterra

  In my last tips about the new OLAP features in DB2 for i 7.3, I discussed the OLAP Aggregation Specification and the new LAG and LEAD OLAP functions. In this article and the next one, I continue the discussion of new OLAP features by highlighting four new OLAP aggregate functions: FIRST_VALUE, LAST_VALUE, nTH_VALUE, and RATIO_TO_REPORT. The value of these functions is that they can reference data from other rows in a query result set relative to the current row. The new OLAP aggregate functions differ from normal aggregate functions (such as MIN, MAX, SUM, and AVG) in that they can

  …

  Read more

• The Basics Of XML-SAX

  June 14, 2016 Jon Paris

  Note: The code accompanying this article is available for download here. In XML-INTO And Optional Elements, I showed a reader how he could use XML-INTO to parse an XML document that effectively contained one of two completely different payloads. As I noted in that article, for this type of “does the document contain X” processing, XML-SAX can be a better choice than XML-INTO. That is the task that I’m going to demonstrate in this tip. In his email, the reader mentioned that his original intent had been to simply identify the type of payload (Report or Event) and

  …

  Read more

• Easier Overloading of SQL Functions

  June 14, 2016 Hey, Ted

  In Formatting Dates with SQL, Take 2, you have shared a great technique. Function overloading is a real boon to SQL programming. Here’s another way to handle the same issue with what I believe is less code and less invasive (meaning you won’t have to recompile programs that use the FMTDATE service program). Leave your original FMTDATE function alone, but add the following to the SQL source: create function xxx/fmtdate (inDate varchar(8), inFromFmt varchar(8), inToFmt varchar(8)) returns varchar(10) language SQL specific FMTDATEA deterministic returns null on null input begin declare DateNum numeric (8,0); declare OutDate varchar(10); set DateNum =

  …

  Read more

Previous Articles Next Articles