• 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

• CHAIN vs SELECT INTO

  August 2, 2016 Chuck Luttor

  The average RPG developer can quickly become proficient in replacing RPG database operation codes with their SQL equivalents when undertaking new programming. In each installment of this series, I will visit an op code or set of op codes in order to prove my contention. First up today is CHAIN. I remember the CHAIN op code from System/3 Model 6 and Model 10 disk days. (Yes, I have been around for a long, long time.) It has been used extensively by every RPG programmer since then. It is the basic op code for random access. In the “old days” it

  …

  Read more

• New OLAP Aggregate Functions In DB2 For i, Part 2

  July 19, 2016 Michael Sansoterra

  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

• 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.

  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

• 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

• 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

• 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

• Inline Table Functions In DB2 For i

  June 14, 2016 Michael Sansoterra

  In DB2 for i 7.2 TR4 and IBM i 7.3, IBM has made a special user-defined table function (UDTF) enhancement that should be shouted from the rooftops. This enhancement is referred to as an inline table function. Consider the simple example of this UDTF named Get_Customer_Orders: CREATE OR REPLACE FUNCTION Get_Customer_Orders ( @CustomerID INT, @StartDate DATE, @EndDate DATE) RETURNS TABLE ( SalesOrderId INT, CustomerId INT, OrderDate DATE, ShipDate DATE, SUBTOTAL DEC(19,4)) LANGUAGE SQL NO EXTERNAL ACTION DISALLOW PARALLEL SET OPTION COMMIT=*NONE,USRPRF=*OWNER,DATFMT=*ISO RETURN SELECT SalesOrderId,CustomerId, OrderDate,ShipDate,SubTotal FROM SalesOrderHeader WHERE CustomerId=@CustomerId AND OrderDate BETWEEN @StartDate AND @EndDate; What does DB2 do when

  …

  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