Ted Holt
-
Digging Out Data Duplication
November 6, 2013 Hey, Ted
… Read moreSometimes an SQL query should return one row, yet it returns more than one. The problem turns out to be multiple matching rows in a secondary table. Is there a way to easily isolate the secondary table that causes more than one match?
–J
Yes, there is an easy way. But first, let me set up the problem for the edification of other readers.
Sometimes we execute an SQL query with the expectation that the result set will contain only one row (record), and we are surprised to get back two or more rows instead. At least one table (physical
-
IFSPOP–Another (And A Better) IFS Interface
August 7, 2013 Hey, Ted
… Read moreNote: The code accompanying this article is available for download here.
I read your article, Another IFS Interface, and I agree with you that IBM‘s green-screen solutions leave a lot to be desired. I got so fed up with WRKLNK, and the even more obtuse WRKFLR, that I wrote my own solution. It’s nothing fancy, but it is a lot better than what exists. It’s a green-screen solution written in RPG.
I modeled it after POP, one of my favorite tools, from the System/36 days. The idea, like POP of old, is that if there is something
-
Don’t Clone That Printer File!
February 6, 2013 Hey, Ted
… Read moreI have a program that uses an externally described printer file to create a report. I need to change the program so that it writes some information to one spooled file and the remaining information to a separate spooled file. I could clone the printer file, but that would mean I’d have to change two files instead of one when I modify the report file in the future. Do I have to clone the printer file?
–Mark
No, Mark, you can build two or more reports from one printer file definition. Here’s how it’s done.
Let’s start with a printer
-
Glenn Wants To Know More Facts About Special Values
November 28, 2012 Hey, Ted
… Read moreI found your article 10 Facts You Should Know about Special Values very interesting. I have a few questions related to a command I am writing.
–Glenn
I owe Glenn an apology. He is one of the many people whose email I never responded to. I’m sorry, Glenn. Sometimes life presents too many opportunities.
Here’s Glenn’s first question:
1. If a user specifies *ALL for a parameter, how do you prevent the user from typing in other values?
Fair enough. Define *ALL in the Single Value (SNGVAL) keyword, not in the Special Value (SPCVAL) keyword.
CMD PROMPT('Do it') PARM KWD(RPTTYPE) -
Use SQL To Update A Sequence Number
November 14, 2012 Hey, Ted
… Read moreIs it possible to use a single SQL statement to assign an ascending sequence number to a column in a table? I’d like the sequence number to start at 10 and increment by 10 as every row is updated so that the number column in the updated rows would be 10, 20, 30, etc.
–Doug
I know a way, Doug. However, let me say up front that I’ve only played with this. That is, I’ve never used it in a production environment. I can’t speak to how practical it might be or what you might need to watch out for.
-
Another Way To Skin A Generic Cat
June 27, 2012 Hey, Ted
… Read moreI am writing in response to your recent article, Add Powerful Generic Processing to Your Applications, regarding generic processing. I have another, simpler technique that you should share with my fellow readers of Four Hundred Guru.
–D
D’s technique is to use the Remove Link (RMVLNK) command. Here’s the example he (she?) gave me:
RMVLNK OBJLNK('/qsys.lib/dug*.devd')This command deletes device descriptions with names that begin with DUG. Notice that RMVLNK requires objects to be specified in the IFS naming convention.
You may use RMVLNK or its two aliases, DEL and ERASE, for objects other than libraries. For libraries,
-
OLAP Is Simple, Once You Understand It
May 9, 2012 Hey, Ted
… Read moreI have a problem that I solved with a simple RPG program, but I know it can be done with SQL as well. I needed to generate a simple table of employee ID, name, and average of the last five weeks paychecks. Is this simple or complicated to do with SQL?
–Barry
You are correct that SQL can do the calculation, Barry. However, you’ll have to decide for yourself if it’s simple or complicated.
First, let’s get some data to work with. I’m going to simplify your question slightly by using a name only, without an ID, to identify an
-
How Do I Format Date Literals? Let Me Count The Ways
May 2, 2012 Hey, Ted
… Read moreI have a strange SQL problem. I have an SQL DELETE command that works perfectly in interactive SQL. But when I use Run SQL Statement (RUNSQLSTM) to execute it, it does not find any rows to delete.
–Jabir
Strange, indeed. I gave Jabir some suggestions, but none of them helped. Jabir found his problem himself. In one of those environments (he didn’t say which), the date format was *MDY, while the date format of the other one was *DMY. SQL was interpreting a date value in two different ways. It’s fortunate that Jabir did not test with a date that
-
NOMAX? No Way!
April 25, 2012 Hey, Ted
… Read moreI just read *NOMAX Does Not Mean Infinite Capacity and I politely beg to differ. I work at some shops with smaller and older systems with smaller disk capacity (70 GB, 140 GB). Some are already using more than 70 percent of available space. If I read the capacities correctly, a physical file can hold over four billion records. I did some calculations and I think I’d fill up a system before reaching capacity.
For DDS-described files, I usually set some maximum number of records, as 10,000 seems ridiculously low. Now that you have mentioned SQL described tables, which I
-
Specify SQL Table-creation Library at Runtime
April 25, 2012 Hey, Ted
… Read moreI have an RPG programs that creates work tables in QTEMP. I’d like to be able to specify the library at runtime. I tried using a host variable for the library in a CREATE TABLE command, but that didn’t fly. I am using the system naming convention. Can you help?
–Ben
I wish CREATE TABLE would allow a host variable for the explicit qualifier, but it doesn’t. Here are a few ways that work.
1. Use dynamic SQL. Embed the library name in the SQL command.
D SqlCommand s 256a varying D WorkLib s 10a /free *inlr = *on; SqlCommand