Ted Holt
-
A Chicken-and-Egg Trigger Problem
February 25, 2004 Hey, Ted
Say I have two physical files: A and B. File A has an insert trigger program (written in RPG IV) that adds new records to B. That is, when someone adds a row to A, the trigger adds a row to B. What would happen if a program read B and added new rows to A?
insert into A (TransID, AnotherValue, Amount) select (TransID, AnotherValue, (0 - Amount) from B where substr(TransID,1,1) = 'T'The intention is to zero-out the balances of all transaction IDs that begin with the letter T. That is, the sum of all records with a
-
A Super Way to Display a Date
February 18, 2004 Hey, Ted
Here’s a subprocedure I wrote that you might want to publish in your newsletter. My routine uses the ILE CEE APIs to format a date as day of week, month name, day, and year, like this:
Wednesday, February 18, 2004
I use the CEEDAYS API to convert a date to Lilian date format, which is the number of days elapsed since October 14, 1582. Then I use the CEEDATE API to format the Lilian date into a character string. In the case of an error (for example, a date before October 14, 1582), I return the ISO character representation of
-
Short Circuit Evaluation
February 18, 2004 Hey, Ted
Here’s a simplification of the code for subprocedure EqAnyNum, which you published on January 28. I was able to shorten the code because of a feature called short circuit evaluation, which is explained in chapter 21 of the V5R2 RPG IV reference.
–Mel
Thanks to Mel for bringing short circuit evaluation to my attention. Here’s Mel’s code.
C/free if %parms >= 2 and BaseVal = Val01 or %parms >= 3 and BaseVal = Val02 or %parms >= 4 and BaseVal = Val03 or %parms >= 5 and BaseVal = Val04 or %parms >= 6 and BaseVal = Val05 -
Dealing with Divided Date Fields
February 11, 2004 Hey, Ted
Some of our ancient database files have separate fields for year, month, and day parts of a date. Combining separate fields to form single date fields is manageable but messy. Building logical files to combine the fields is not always practical, although we have done some of that. When digging around on Google one day, I accidentally discovered an SQL function that is well-suited for divided date fields.
The function I discovered is called DateSerial. It is a part of various software products, including Microsoft Access and various versions of Visual Basic. It requires three arguments: year, month, and day.
-
The Better Way to Delete Physical Files
February 11, 2004 Hey, Ted
You missed a possible link between two articles in the January 14 issue! The first article is “Why SQL, Why Now,” and the second is “Deleting with a Generic File Name.”
The missing link is that, if you use SQL (to define your database) and issue the command DROP TABLE, the indexes and logical files are deleted (dropped) also! So that’s one of the many reasons why you should use SQL.
–Wim
Thanks for the advice, Wim. Several other readers wrote with similar comments.
SQL’s DROP TABLE command will indeed drop a physical file and the logical files that
-
How Many Records Did SQL Delete?
February 4, 2004 Hey, Ted
I have an RPG IV program with an embedded SQL DELETE statement. Is it possible for my program to determine how many records are deleted when the statement executes?
–Tom
DB2/400 returns this information to you through a subfield of the SQL communications area (SQLCA), a data area that is automatically included in your RPG program when you compile. You can refer to this subfield by either of two names: SQLERRD(3) or SQLER3. When a DELETE, UPDATE, or INSERT operation completes normally, DB2 updates the subfield with the number of rows that qualified for the operation.
C/exec sql C+ delete
-
Simplify Complex Conditions
January 28, 2004 Hey, Ted
One of the features I like about Open Query File is the %VALUES function, which allows me to determine whether a value is found in a list of other values. For example, the expression CTYPE = %VALUES(45 56) is equivalent to CTYPE = 45 or CTYPE = 56. RPG does not have a similar function. Is there no way to simplify complex conditions?
–David
For record selection with SQL, you can use an IN predicate with a set. However, that’s not what you’re asking for in this case.
One of the nice things about ILE is that you can
-
Retrieve the System Name
January 28, 2004 Hey, Ted
At the end of reports, I print a special line that indicates that the report is complete. In addition to the message “end of report,” I print the qualified job name, qualified program name, and in the case of SQL programs, the last value of the SQL status variable. These values help me to debug and troubleshoot problems. I’d like to add one more bit of information.
We recently got a new machine with logical partitioning, and we have set up a partition to use as a test environment. I would like to add the system name to the end-of-report
-
Deleting with a Generic File Name
January 14, 2004 Hey, Ted
I’ve been working on the AS/400 for almost 15 years now, and I’ve always thought that a physical file could not be deleted until all the logical files based on the physical file had been deleted. However, while working on a problem dealing with deleting a physical file when a logical may or may not exist, I tried using a generic file name. My reading of the DLTF command description led me to believe that the files would not be deleted. To my surprise, the files–the physical file and the two logical files–were deleted. Did I misunderstand the description? Is
-
Functions Can Modify Parameters, Too
January 14, 2004 Hey, Ted
Steven Gray suggested that it’s a good idea for an RPG function subroutine to return a data structure. I think he’s on the right trail but barking up the wrong tree. Barbara Morris pointed out that it’s more efficient to modify a data structure as a parameter of a subprocedure. I suggest that the best approach is to combine the strengths of the two methods.
I understand and fully appreciate the enthusiasm many programmers feel for functions. Compared with traditional RPG coding methods, nesting an %INT function within a %SUBST function within a %CHAR function, for example, eliminates