Editors: Ted Holt and Howard Arner Managing Editor: Mari Barrett
This issue of the OS/400 Edition of Midrange Guru is sponsored by:
Topics Covered In Volume 1, Number 3:
Hey, Ted :
Is it possible through SQL to remove a character from a text field? I have a file that has a name field with the last name first and first name last. Some of the records were keyed with a comma separating the two names, as in SMITH, JOHN. Other records have no comma. I would like to remove the commas. The names are all different lengths so I can't use any kind of constant length.
Yes, SQL can handle this problem for you. First, select the records that have the comma, because those are the only ones you want to update. I doubt you have any records with a comma in the first position of the field, but just in case, exclude them by checking for a comma anywhere after the first position.
select * from customer
Use the position function again to extract the last and first names, which precede and follow the comma.
Now that the select is working correctly, convert it to an update statement.
Be sure to try this on a copy of the production file first to verify that your SQL command will work correctly.
To unsubscribe, change your email address, or sign up for any of Guild Companies, Inc's free email newsletters, visit http://www.itjungle.com. Hit the SUBSCRIBE button on the homepage and it will lead you to our online subscription system.
When you sign up for one of our e-newsletters, you can be assured that your e-mail address will NEVER be sold to an outside company.
Please contact Timothy Prickett Morgan at
Phone: 212 942 5818
Our programmers need a list of the error messages that can be monitored in CL programs. Such a list used to appear in the Programming Reference Summary manual. I've looked in the Information Center and it refers me to the CL manual, which doesn't give me the error messages to monitor. I have an OS/400 V3 manual, but we are currently running OS/400 V4R5, and many new commands have been added to the CL language since then. How can I get a list of error messages?
Here's how. The iSeries Information Center does provide a list of commands that can be monitored. Each command is followed by its syntax diagram. The iSeries Information Center's alphabetical listing of commands can be found at http://as400bks.rochester.ibm.com/pubs/html/ as400/v5r1/ic2924/info/rbam6/rbam6alphatable.htm.
But there's an easier way to look them up. From a command line or within OS/400's Screen Entry Utility (SEU), type the name of the command that you want to monitor and press F4 to prompt it. The cursor will be positioned to the first entry field. Move the cursor up to a higher line and press F1 to get the help text for the command. Within the help text, you will see the escape, status, and notify messages that can be monitored.
One of the great things about the OS/400 community is that it is indeed a community. We may be all working from our cubicles, but we are all connected and trying to figure out how to best employ the computer technology at our disposal. There are more than a few ways to skin any cat, and if you have a clever and unique answer to a problem that one of our Midrange Gurus has solved, we'd love to hear from you. This newsletter is an open dialog, and we value your input as well as your readership.
It goes without saying--but we'll say it anyway--that your hard technical questions pertaining to real world problems are equally valuable as a foundation for this newsletter as are your programming insights. We hope you find all the editions of Midrange Guru valuable, and we are going to work hard to make sure that they are.
If you have a tough problem, our gurus can probably help. Their mailboxes are always open.
* Email Ted Holt at firstname.lastname@example.org
* Email Howard Arner at email@example.com
Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved. This document may be redistributed freely and enthusiastically by email, but only in its unedited form. Thanks for your cooperation. Midrange Guru is a registered trademark of Guild Companies, Inc. IBM, AS/400, iSeries, OS/400, and eServer registered trademarks of International Business Machines Corp. All other product names are trademarked or copyrighted by their respective holders.