End-Of-Year Feedback

December 4, 2013 Hey, Esteemed IBM i Professional

I hope 2013 was a good year for you. It was my best year in a while. I left a dysfunctional bureaucracy for a company where I can make a difference. I renewed several old acquaintances. And I found out from LinkedIn that I know someone who knows someone who knows Haynes Brooke, the guy who plays the sun in the Jimmy Dean sausage commercials. Let’s close out the year with some of the feedback you’ve been kind enough to send to me.

Hey, Ted:

In Alan’s Easy Method For Building A CSV File From A Report, you mentioned that some programs have issues with the trailing negatives common in AS/400 reports and screens, and Excel is one of them. This Excel macro adjusts selected data, whether you imported a text file or copy and pasted from a screen. It can be handy when you’ve imported a spool file or copy and pasted from an AS/400 screen. I use it all the time when I am checking out a new report or a screen that totals.

–Lynne

Thanks to faithful reader Lynne Noll for this macro. Not only does it correctly interpret a trailing minus sign, it correctly handles the commas we use here in the U.S.A. to separate groups of three digits.

Sub SelTextToNbr()
'
' SelTextToNbr Macro
' This converts the trailing negatives
' when you paste AS400 data into Excel
' Works on selected text
' Macro recorded 6/10/2009 by Lynne Noll

Dim Cell As Range
If Selection.Cells Is Nothing Then
Exit Sub
End If


For Each Cell In Selection.Cells
If Cell.HasFormula = False And IsNumeric(Cell) = True Then
      If Cell.Value <> "" Then
         Cell.Value = CDbl(Cell.Value)
      End If
End If
Next
End Sub

Hey, Ted:

You have no idea how much I value your articles. I used your prompt and submit article as the basis for a utility that prompts the user for a list of programs and returns a RSTOBJ command listing all files used by those programs on the OBJ parameter. I also enjoyed the one on creating help text for commands. Simply awesome!

–Alan

I have to give credit for that help text creation technique to a former co-worker of mine, Chris Wages. He is outstanding!

I always look forward to your easy-to-read and practical writings. Others write about things you may use once in a blue moon and have to read through five times just to understand the first paragraph. Not you!

–Tom

I read your article Thinking In Pointers and the CL portion help solved a quandary I had regarding a command I had written. Thanks for the very first article explaining pointer logic to me in a manner that I can (sort of) understand.

–Mark

I love this platform but it is so vast now. When I go to one shop, they are on one side of the spectrum, and when I go to another shop, I end up using something completely different. At the last place I worked, they were creating tables and views with SQL and using stored procedures (not the complex ones, thank God), but were not using service programs much and had no plans for using/managing activation groups.

I love the article you had on Adding Subprocedures to a Service Program. Service programs look like a great way to organize and reuse software.

I started on an IBM 360/20 and IBM 360/40 at a service bureau on third shift. The company was profitable and we stayed pretty busy at the time. “Old” for sure, I’m 60 now, and I just heard a friend of mine just went back to work at age 68!

–Jake

If you liked that article, Jake, read the follow-up.

Thank you for your most recent article on SQL and decimal data errors. The information is particularly germane to this shop and will most certainly come in handy.

–Dave

Evidently a lot of shops still struggle with non-relational data, Dave.

I just want to thank you for your tip Change a Substring with SQL. Whereas I am fairly fluent in SQL, I was struggling to update a field in positions 1 to 8, but with a requirement for positions 9-256 remaining as x’00’. I had tried all sorts of methods–CONCAT NULL–seeing if there was a RPAD function easily available I could use, etc. This method fit the bill exactly.

–Mike

I just wanted to say thanks. I used your tip Mismatched Record Name Formats, published on February 18, 2009, concerning how to create logical files to use with OVRDBF. It works great to keep the RPG code elegant.

–David

The question came from a different David.

Regarding your article Don’t Clone That Printer File!, if you’re on 7.1, you can get rid of all the overrides with the new EXTDESC keyword: Fqcustcdt if e k disk.

Freport1   o    e             printer
F                                     extdesc(qad001p)
F                                     extfile(*EXTDESC)
F                                     rename(header: header1)
F                                     rename(detail: detail1)
F                                     rename(footer: footer1)
Freport2   o    e             printer
F                                     extdesc(qad001p)
F                                     extfile(*EXTDESC)
F                                     rename(header: header2)
F                                     rename(detail: detail2)
F                                     rename(footer: footer2)

EXTDESC removes the need for compile time overrides and EXTFILE removes the need for run-time overrides. AFAIK work on printer files and display files along with disk files.

–Charles

Quite a few readers sent me this same information.

Regarding Anita’s tip #4:

SQL has a COUNT DISTINCT function, so, if you want to count the number of distinct customers in your contract file, you don’t need a common table expression.

select count(distinct cacst) from cntmst

–Doug

How easily we forget such things. There’s too much to remember.

I enjoyed Anita’s SQL Tips.

Regarding tip number 4:

WITH list as(
SELECT distinct cmcust FROM cacmst
)
SELECT count(*) FROM list

I’ve found an easy count(*) tip that I use all the time now. I came across it when trying to do a count(*) with multiple distinct values (SELECT COUNT(DISTINCT fld1, fld2…), which doesn’t work. So now I do this:

SELECT COUNT(*) FROM (
SELECT DISTINCT fld1, fld2...FROM table
) T

The beauty of this is I can do my regular selects for testing. When I want a count I just put the “SELECT COUNT(*) FROM (“above my regular select and then a”) T” and now I get a count without re-writing my original select. You can use any table identifier. I just use T. It works great.

Thanks as always for helping me add to my toolkit.

–Russ

I hadn’t thought about the problem of count distinct against two or more values. Good idea!

About the article Using SQL Joins With Tree Structures, you could also have used a common table expression to do the same thing.

INSERT INTO PRODEXPL (FINISHGOOD,
                      QTYPER1,
                      PARENT,
                      PARENTTYPE,
                      QTYPER2,
                      COMPSEQ,
                      COMPONENT,
                      COMPTYPE,
                      LEVEL)
   WITH FG (FINISHGOOD, QTYPER_1, QTYPER_2, PARENT, 
   COMPONENT, COMPSEQ, LEVEL)
        AS (SELECT PROD.ITEM,
                   1,
                   1,
                   PROD.ITEM,
                   PROD.ITEM,
                   0,
                   1
              FROM PRODUCTS PROD
             WHERE PROD.TYPE = 'FG'
            UNION ALL
            SELECT PARENT.FINISHGOOD,
                   PARENT.QTYPER_1 * CHILD.QTYPER,
                   CHILD.QTYPER,
                   CHILD.PARENT,
                   CHILD.COMPONENT,
                   CHILD.COMPSEQ,
                   PARENT.LEVEL + 1
              FROM FG PARENT, PRODSTRUCT CHILD
             WHERE PARENT.COMPONENT = CHILD.PARENT)
     SELECT FINISHGOOD,
            QTYPER_1,
            PARENT,
            P1.TYPE,
            QTYPER_2,
            COMPSEQ,
            COMPONENT,
            P2.TYPE,
            LEVEL
       FROM FG, PRODUCTS P1, PRODUCTS P2
      WHERE PARENT = P1.ITEM AND COMPONENT = P2.ITEM
   ORDER BY FINISHGOOD,
            LEVEL,
            PARENT,
            COMPSEQ;

–Martin

Maybe I should rethink the next-to-last paragraph of that article. I don’t know if I could explain the recursive subquery. My hat’s off to you for figuring it out, Martin. Thanks for sharing it with me.

Thanks to everybody who contributed tips, offered suggestions, encouraged me, and most importantly, read this newsletter. I enjoyed putting it together this year, and I am happy to know that many people found helpful information.

May 2014 be a year of wonder. May you ponder the mysteries of life, such as “Why does the text of message SQL6789 end with a period instead of a question mark?”

Enjoy the holidays! See you next year!

–Ted

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search