Ted Holt
-
The SPACE Function Takes Up a Lot of Space
October 18, 2006 Hey, Ted
… Read moreI have to build a file for input to another system. All fields are fixed-length, and numeric values are to be edited and right-adjusted. SQL’s CHAR function takes care of the editing requirement, but the result is left-adjusted. How do I right-adjust an edited numeric value? Or will I have to rewrite my program using native I/O operations?
–Sarah
On the surface, this seemed like an easy request. It turned to be a little more complicated than I had thought.
I suggested Sarah use the SPACE function to generate enough leading blanks to force right alignment. For example, the following
-
CL Odds and Ends
October 11, 2006 Hey, Ted
… Read moreI hope this question is not too simple for a newsletter that claims to be for gurus. An RPG program or module retains variables, open data paths, and other resources from one invocation to the next, as long as the LR indicator is not on. CL programs and modules, on the other hand, suffer from Alzheimer’s disease. They don’t remember anything from one invocation to the next. How do you handle this limitation?
–Richard
I can help you with part of this problem, Richard. Create a temporary data area to hold the variable values from one call to the next.
-
Use SQL to Remove Extra Spaces
October 11, 2006 Hey, Ted
… Read moreThe SQL sorting tip you published on September 13 was a good one. I had seen a similar tip written by Craig Mullins in Quest Software’s Pipeline Newsletter. Mullins had two other interesting tips on the same page. I thought your readers might like to see them as well.
–Tom
The Web page to which Tom refers is at http://www.quest-pipelines.com/newsletter-v7/0606_F.htm. Craig Mullins uses the technique about which I wrote to sort data on three-letter day abbreviations.
Another tip he presents involves using the REPLACE function to remove extra spaces within a character string. IBM added the REPLACE function
-
Kevin Juenemann’s Quick Query Utility
October 4, 2006 Hey, Ted
… Read moreNote: The source code for this article is available for download
Your QF command works great when you want to see the raw data, but sometimes you need only some of the columns, or need to join files to get usable data rather than codes. Many of our customers don’t have interactive SQL installed on their systems, so I created this utility and sent it to all our customers for those times a one-off report is needed quickly.
I can enter an SQL Select statement and a printer ID, and the utility will create a Query Management Query and run
-
Remove Blank Lines from a Stream File
October 4, 2006 Hey, Ted
… Read moreWorking with IFS files, especially those we get from other systems, greatly increases my appreciation of physical files. Opening a stream file is like opening a Christmas present–we never know what we’ll get. Anyway, one source typically sends us stream files that often have blank lines in them, which messes up our process. Is there any way to delete those blank lines?
–Ray
Well, Ray, I don’t know of a way to delete them, but if you’re willing to copy the stream file to another stream file and omit the blank lines in the process, you can take advantage of
-
Redirecting a List of Qshell Commands
September 20, 2006 Hey, Ted
… Read moreI need a little help with a Qshell script. I retrieve a list of IFS files that have a certain extension (a suffix precede by a period) and pipe the list into another process. Now I have to also include other files that have a completely different extension. I do not want to run the command twice, processing each type of file in turn. How do I redirect the output of more than one command into one output stream?
–Brad
OK, let’s set up the problem for the benefit of the readers. Suppose Brad’s script currently processes files with the
-
Include Comments in Query/400 Queries
September 20, 2006 Hey, Ted
… Read moreOK, I’ll bite. In your article Query/400 Handles Zero Dates (Sort Of), from Four Hundred Guru Volume 6, Number 25, you said, “This is one way to include comments in a query.” Does that mean there are other ways I can include comments in queries?
–Paul
I know of one other way to comment result fields other than the one I used in that example. First, let’s review that method. Here’s the first result field in that article.
Field Expression Column Heading Len Dec SDNOT0 shipdt/(shipdt-0.0000001) 0=Ship date is zero 1 0 1=ship date is not zeroIn this
-
Unusual But Logical SQL Sorting
September 13, 2006 Hey, Ted
… Read moreA physical file that I’ve loaded with data from another system has a department field that I would like to use for sorting. The department field contains a description, rather than a code. I need to sort the data on the description, but not in alphabetical order. That is, I want a certain department to appear first, then a certain department to appear next, and so on. The only way I know to pull this off is to create a little table that lists the departments and the order each one appears in the sort, but I do not like
-
Undocumented Debugger Function
August 30, 2006 Hey, Ted
… Read moreHere’s a tip for those readers who, like me, still have to use the full-screen, green-screen debugger. I accidentally discovered that RPG supports a debugging option that is not documented in either the help text or the RPG programmer’s guide.
If you want to break a program or procedure when a variable has a certain value, you can use a conditional breakpoint. In the following example, the program breaks at statement 12 if variable STATE has the value TX.
break 12 when state = 'TX'
Suppose you want to stop when part of a field contains a certain value? The
-
EDTF and End-of-Line Delimiters, Take Two
August 30, 2006 Hey, Ted
… Read moreIn EDTF and End-of-Line Delimiters, you demonstrated a method for removing carriage-return characters from end-of-line delimiters in stream files. Unfortunately, your method only works in interactive jobs. I need to do the same operation on a file that I need to FTP. Do you have a way to remove these characters in batch?
–Chuck
You have pointed out yet another violation of Holt’s Law of Interactive Programs, which states: “The problem with interactive programs is that you can’t run them in batch.” I have a few methods for you, Chuck. First, here’s one from Shalom Carmel, a really sharp