Ted Holt
-
CL’s Null Value
April 19, 2006 Hey, Ted
… Read moreI am writing a CL program in which I pass variables to the Change User Profile (CHGUSRPRF) command. The command fails when I try to use a qualified name containing a single value like *SAME or *NONE. If I use one variable for the qualified parameter, *SAME and *NONE work, but the compiler won’t allow me to put both library name and program name into one variable. How can I pass qualified names that allow single values as variables to commands?
–Brian
Use two variables for the qualified name in your CHGUSRPRF command. When you need to use a single
-
Change a Substring with SQL
April 5, 2006 Hey, Ted
… Read moreI am trying to change part of a character field with an SQL UPDATE command. However, my SQL command gives me an invalid token error at the opening parenthesis of SUBSTR in the SET statement. Is there a rule that you can’t use SUBSTR in a SET statement? If so, do you have a workaround?
–Dennis
What you’re trying to do is reasonable, Dennis. After all, RPG and CL let you change part of a character field using their %SUBST and %SST functions. But SQL is different.
For the benefit of other readers, here is the SQL command Dennis sent
-
What’s New in V5R4 COBOL?
March 29, 2006 Hey, Ted
… Read moreI enjoyed the V5R4 RPG and CL enhancements articles. I can’t wait to use subroutines to clean up code in CL programs. Also, the RPG and SQL tips and techniques are great. But those of us who labor in the COBOL fields could use a little help.
–Thom
It’s taken me some time to get to it, Thom, but thanks to Philip Mawby, of IBM‘s Toronto software labs, I have the information you need. I’ll hit the high points. Consult the V5R4 COBOL reference for more details.
XML Support
In V5R4, COBOL stays ahead of RPG in the area
-
How Many Rows Did SQL Fetch?
March 29, 2006 Hey, Ted
… Read moreI am using SQL to load a subfile. Since there are 16 subfile records to a page, I fetch 16 database records into a data structure. However, the fetch does not always return 16 rows. Sometimes it returns fewer. How can I tell how many data structure occurrences were fetched?
–Lynn
You will need to check the SQLER3 field of the SQL Communications Area (SQLCA). I wrote about this field earlier in Four Hundred Guru. See “How Many Records Did SQL Delete?”
Here’s an example I threw together that you can start from. It fetches seven records at a
-
How to Cancel a Job
March 15, 2006 Hey, Ted
… Read moreI have what I hope is a simple question. A CL program that runs in batch mode calls an RPG program, passing parameters to it. If the RPG program finds a certain error, it sets one of those parameters to a certain value, sets on the LR indicator, and ends. At this point, the CL program ends, but the message that it generates gives the caller the impression that the job ran OK. How do I make the program end abnormally?
–Mike
That’s a good, basic question, Mike, and I understand why you bring it up. This phenomenon drove me
-
Readers Write about the INCR and RRN Functions
February 22, 2006 Hey, Ted
… Read moreI liked your article, Numbering Rows in an SQL Result Set, but I think your INCR function may be overkill. I’m able to accomplish the same thing with the RRN function. Would you please elaborate on the difference between the two methods?
–Rafael
Rafael, you need to take a look at the results of the slightly modified SQL query you sent me. Here’s the query.
SELECT INCR(9999), (RRN(qcustcdt) + 9999), LSTNAM, INIT, CUSNUM FROM qiws/qcustcdt ORDER BY LSTNAM, INITAnd here are the results.
INCR RRN LSTNAM INIT CUSNUM 10000 10011 Abraham M T 583990 10001 10006 Alison J S
-
Work Fields and SQL
February 15, 2006 Hey, Ted
… Read moreI haven’t been able to get SQL to handle result fields in the same way Query/400 can. In Query, I can define a result field, then use that field in the definition of another field. If I try this with SQL, I get a message that says, “Column X not in specified tables.” Is there a way to avoid duplicating the calculations of a work field?
–Erik
No, Erik. Not with SQL/400. To my knowledge, the only SQL-based software product that permits you to define work fields is Advanced System Concepts‘ SEQUEL. But I can give you some workarounds
-
A FUNction to Align Text
February 1, 2006 Hey, Ted
… Read moreI read with interest your article on the IIF function. I have a function called alignText that I use in just about every program. I use it primarily to center headings, but have found it useful for right adjust as well. I have had this for a long time, so it is in traditional form, not freeform. Notice that there are three optional parameters. The alignment defaults to centering, and then two additional parameters are used to change the display attribute if I want to.
— Gordon Larkins, ASK Senior Technical Consultant
It was nice of Gordie to share his
-
WDSc’s Outline View is Handy
January 18, 2006 Hey, Ted
… Read moreFor developers using Websphere Development Studio Client (WDSc), here is a tip that has been worth its weight in gold to me. The outline view is a very powerful tool. It can be enhanced even more than the default way it is loaded. For example, in the default mode the outline shows the files that are defined, the record format, and all the fields. Outline view can also show the source line numbers where the fields, indicators, subroutines, etc. are accessed. Clicking on a line number in the Outline view takes you to that line in the LPEX editor. This
-
Alternate SQL Row-Selection Criteria, Take 3
January 11, 2006 Ted Holt
… Read moreTwo previous issues of this newsletter have presented ways to put two or more record-selection alternatives into one SQL query. I use these techniques often because they reduce or eliminate the need to clone programs. I recently learned another way to implement alternate record-selection criteria and would like to share it with you today.
Let’s begin with an example table of people.
create table mylib/people ( FirstName char(12), LastName char(12), Street char(20), City char(16))Here’s the data I placed into my copy of the PEOPLE table.
FIRSTNAME LASTNAME STREET CITY ========= =========== ================= ============ Smith Whiteside 451 Elom Street Lost