Ted Holt
-
Formatting Dates with SQL, Take 3
November 17, 2015 Hey, Ted
… Read moreGreat technique shared! Function overloading is a real boon to SQL programming. Here’s another way that requires less code and is less invasive (i.e., you won’t have to recompile anything.)
Leave your original FMTDATE function alone and 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 = decimal(inDate); set OutDate = FmtDate(DateNum, inFromFmt, inToFmt); return OutDate; end
I’ve used this technique to overload many user-defined functions and keep the HLL programming
-
Beware The Temporary Table
June 2, 2015 Hey, Ted
… Read moreI am writing in response to your article Dynamic Lists in Static SQL Queries. At a recent NEUSG meeting, Tom McKinley of IBM warned us to avoid temporary tables, especially query chains of temporary tables, as they have no history for the optimizer. I like dynamic SQL. I would have left it alone.
–Lynne
I always enjoy hearing from Lynne because I know I will hear words of wisdom. Lynne raises a good point that I have intended for some time to address in this august publication.
Kent Milligan, one of Tom McKinley’s colleagues at IBM’s DB2 for i
-
Formatting Dates with SQL, Take 2
May 5, 2015 Hey, Ted
… Read moreI am trying to use your FMTDATE SQL function but the system tells me it can’t find it. I know FMTDATE is there. Can you help?
–Robert
Note: The code accompanying this article is available for download here.
I’ve received a compliment or two (at least) for the FMTDATE (format date) function I published back in 2009. (Can it really have been so long? How time flies!) I use it heavily, and I’m not the only one. If you’re not familiar with FMTDATE, please read Formatting Dates with SQL before continuing.
To address Robert’s question, the reason the system
-
Do It Now!
August 13, 2014 Hey, Ted
… Read moreIn our RPG programs we protect input operations (CHAIN, READ, etc.) with an error-handling routine that notifies users of record locks. That is, the RecLock subroutine sends a break message to the locking user and displays a window with lock information to the requesting user. This logic is not working in one of our programs, and we can’t determine why. Can you help?
–Kent
I’m glad Kent wrote with this problem. It gives me an opportunity to write about a topic that has been on my mind for some time.
But first, here’s a trivia question for you to think
-
Search Source Members For Two Or More Strings
April 2, 2014 Hey, Ted
… Read moreI have used option 25 of the Work with Members Using PDM (WRKMBRPDM) for years to look for character strings in source physical file members. Is there a way to search for two or more strings at once?
–T.
It’s not possible with that option. You might want to use option -e of Qshell’s grep utility.
Let’s say you want to find all instances of invnbr and invoicenumber in source physical file SRC in library TST. Here’s the command you would need to use:
$ grep -in -e 'invnbr > invoicenumber > ' /qsys.lib/tst.lib/src.file/*
The first, third, and fifth lines
-
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.