Ted Holt
-
Replacing Source in The Twenty-First Century
October 18, 2016 Ted Holt
… Read moreIn Searching Source in The Twenty-First Century, I introduced the use of regular expressions for searching within a source member. Searching is great, but sometimes you need to replace what the system finds with something else. Here’s how to replace text when using regular expressions.
Let’s begin with a source member.
Notice that there are nine constants named Stat10 through Stat90. Let’s replace Stat with Status_. Obviously we can’t replace Stat with Status everywhere it’s found. That would be disastrous!Instead, let’s use a regular expression to find the instances where Stat is followed by a digit.
I -
Searching Source In The Twenty-First Century
October 4, 2016 Ted Holt
… Read moreI love it when IBM gives me a way for me to do something I couldn’t do before. When I think of the tools I had to work with three decades ago, I could almost weep. Recently I had to search a source member for a string of characters, and I was overjoyed that LPEX gave me what SEU couldn’t.
To set the stage for my requirement, I should mention that as a matter of practice, I qualify data structures in my RPG programs. For me to omit the qualified keyword, I have to have a reason.
dcl-ds Status qualified;
-
Easy Printing from CL, Take Two
August 30, 2016 Hey, Ted
… Read moreWhen I read your statement that CL cannot write to printer files, I immediately thought, “It can’t? Then what have I been doing for the last couple of decades?” I looked through the article and saw that it didn’t mention my most used method, and I wondered how it was missed.
–Tom Liotta
Tom takes advantage of one of the best features of ILE, namely the ability to bind routines written in different languages to form one program. The non-CL procedure that he included is the printf function, which is well-known to C programmers. Even though printf produces stream
-
Message Data Data Structures
August 30, 2016 Hey, Ted
… Read moreSending Escape Messages from RPG is a great article. Your program defines the message data parameter as 80 bytes of character data, but the IBM Knowledge Center defines MSGDTA as char(*) with notes saying it can be up to 32,767 bytes. I would like a variable longer than 80 bytes, but instead of coding 100 today, 120 next project, and so on, how could I code it to take full advantage of the API?
–Glenn
You’re right that the API can handle up to 32K of data, but you need only define MsgDta as large as you need it to
-
How To Insert With A Common Table Expression
August 16, 2016 Hey, Ted
… Read moreI have to merge some new item numbers into our item master file. The problem I’ve run into is that there is more than one record for some items. None of the examples I found on the Web work for me. How do I write an SQL statement to copy all records from one file to another avoiding duplicates on part number?
–Trent
This is a new wrinkle on a challenge about which I have written before, namely the need to select only one row (record) to represent a group. (See the Related Stories below.) The wrinkle is that not
-
Side-By-Side Lists
July 19, 2016 Hey, Ted
… Read moreAn 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.
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
-
Easier Overloading of SQL Functions
June 14, 2016 Hey, Ted
… Read moreIn 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 =
-
Sending Escape Messages From RPG, Take 2
May 10, 2016 Hey, Ted
… Read moreIn Sending Escape Messages from RPG, your program defines the message data parameter as 80 bytes, but the IBM manual (Send Program Message (QMHSNDPM)) defines the parameter as char(*), with notes saying it can be up to 32767. I would like a variable longer than 80, but instead of coding 100 today, 120 next project, and so on, I’m wondering how I could code it better to take full advantage of the API.
–Glenn
The short answer is that you don’t have to code the length. Let the compiler figure it out for you. Here’s an illustration that may
-
Upsert One Row From A Data Structure
January 26, 2016 Hey, Ted
… Read moreI’m attempting to use the SQL MERGE statement to write an “upsert” that stores the data to be inserted or updated in an externally described data structure. Is that possible? Any insight into this would be helpful.
–Lewis
Even though Lewis was only dealing with one row in the database table, what he was doing qualifies as an upsert in my book. He’s updating a row if it’s there and adding it if it isn’t. However, I don’t think MERGE is the right tool for this job.
MERGE is designed to update a lot of rows in one table from