Ted Holt
-
ON vs. WHERE
November 14, 2007 Hey, Ted
… Read moreI need your help with an SQL query. I have two database files: a header file and a detail file. There may be many detail records for each header record, but I only want to see certain detail records. Here’s the rub. I want to retrieve all header records, even the headers for which I don’t select any corresponding detail. It seems to me this should be an easy query to write, but my efforts so far produce only the header records for the selected details.
–Wayne
There’s more to this query than meets the eye, Wayne. It is simple,
-
Copy Message Descriptions
November 7, 2007 Hey, Ted
… Read moreThe Work with Message Descriptions (WRKSMSGD) panel gives me options 2, 4, 5, and 6 to change, delete, display, and print message descriptions. But there is no option to allow me to copy a message description. Such an option would be helpful when I need to create a new message description that is similar to an existing one, or when I copy a message description to another message file. Am I overlooking an easy way to copy message descriptions?
–Ron
To the best of my knowledge, no. (Buddy, I hope I’m wrong on this one.)
Let’s take the second case
-
Converting LF to CRLF in IFS Files
October 17, 2007 Hey, Ted
… Read moreI had a situation where I wanted to change the end-of-line character from linefeed to carriage-return/linefeed to make a JavaScript file on the Integrated File System more readable in Notepad. I stumbled upon EDIT FILE (EDTF), F15, option 5. Then, after a little searching, I found your articles. Talk about reinventing the wheel! I was surprised that changing end-of-line characters was not part of the CHANGE ATTRIBUTE (CHGATR) command (or some other command). Is there any chance you could share a Qshell solution that converts LF to CRLF? I love using Qshell when I can, but
-
Weird SQL UNION Results
September 26, 2007 Hey, Ted
… Read moreI was intrigued by your article Have Your Cake and Eat It, Too. I decided to try your tip with some of my own data. The resulting data set had an anomaly in it. The system omitted a row of input, yet the total was still correct. Why?
–Wyatt
It’s my bad, Wyatt, and I should have known better. (Note to self: Leave instructions for my survivors to put those last five words on my tombstone. It seems to accurately describe my life so far! 😉
The problem was that the example used the word UNION, but it should
-
Reuse Deleted Records? *YES!
September 12, 2007 Hey, Ted
… Read moreHow does the iSeries find a space to insert a new record over a deleted record when REUSEDLT(*YES) is specified for a file? An associate says that the file is scanned sequentially for a deleted record when writing a new record to the file and as a result there is a significant performance penalty.
–Millard
I sent your question to Kent Milligan of IBM. According to him, “A table scan is not performed. A bitmap-like structure is used to identify available deleted rows within the table.”
So, Millard, there are no performance-related reasons not to reuse deleted records. This topic
-
I Want My F15 Back!
August 29, 2007 Hey, Ted
… Read moreI’m trying to get up to speed with WDSc. It’s getting better, but I’m not out of the woods yet. One SEU feature that I really miss is the ability to press F15 and browse another member. Using the Remote Systems Explorer and LPEX editor, I find myself following the navigation tree in order to open a member. Please make my day by telling me that LPEX has a function equivalent to SEU’s F15 key.
–Rick
OK, Rick, I’m going to make your day. LPEX has such a function.
While you are editing, hold down the control and shift keys,
-
Commands with Generic Parameters
August 22, 2007 Hey, Ted
… Read moreA while back you posted a tip regarding DLTF with a generic file name. It works great! I needed to copy a physical file and its related logical files to another library. I decided to prompt the CRTDUPOBJ command and was delighted to find that the OBJ parameter can specify a generic name! The file names were similar, with a slight difference in their suffix, so I was able to duplicate multiple objects with one command. Lesson learned: prompt a command once in a while and read all the parameters. Something may have been missed or added in a
-
Which One Is the Culprit?
August 8, 2007 Hey, Ted
… Read moreI am using the MAX function in a SQL statement to get the maximum value from a few columns. I don’t know which element is producing the maximum value. Is there a way to identify the element that has the maximum value?
–Hisham
Yes, Hisham. Thanks for allowing me another opportunity to show off that most versatile of SQL features: CASE.
For the benefit of the readers, here’s the SQL statement Hisham sent to me.
SELECT max(WK01, WK02, WK03, WK04) FROM SALES
Here’s the sort of results his SQL query generates.
MAX === 100 120 150
He has the maximum
-
Have Your Cake and Eat It, Too
August 1, 2007 Hey, Ted
… Read moreRecently one of my superiors walked into my cubicle and asked for some information. I was in the middle of another task and didn’t want to be side-tracked, so I hurriedly ripped out an SQL query to satisfy his request. A little while later he was back in my office asking me to run a fresh copy of the query, and while I was at it, to total some of the columns. This last request threw me for a loop, so I used Query/400. Could I have satisfied his request with SQL?
–D
I thought I had covered this situation
-
Bring Back My Qshell Output
July 18, 2007 Hey, Ted
… Read moreI just ran a CL program that has an embedded QShell script. It produced some output to my display and gave me a message that told me to press Enter. I did, and the Qshell output display went away. Now I want to bring that display back and look at the output again. Can I do that?
–Len
Yes, and it’s easy. So the readers can see what you’re talking about, let’s look at an example. Here’s a CL program that includes a QShell script.
pgm dcl &Script *char 1024 dcl &QshStatus *dec 10 value(0) dcl &MsgDta *char 256 dcl