SQL’s One-Row, One-Column Table
October 30, 2002 Timothy Prickett Morgan
Hey, Ted:
In the October 25 Midrange Guru article, “SQL to the Rescue!“, a reader named Jeff provides an example that requires a file with one record. IBM provides such a file for just that purpose:
|
It’s called QSQPTABL.
— Yannick
Thanks for the information, Yannick. QSQPTABL is in library QSYS2. It has one column (field), which is a four-byte integer field called INTCOL (INTEGER_COLUMN). The only row in the table has the value 1.
It occurred to me that this technique is probably worth further exploration. SQL has a rich set of scalar functions, and IBM adds more functions with new releases. Besides, enterprising programmers can write functions of their own. Learning to use this selection technique against one record provides a way to tap into these functions.
Here are three more examples to help the reader who is not accustomed to using SQL to carry out internal data conversions in RPG. The first example uses SQL’s DAYOFWEEK function, which returns a number from 1 to 7 to indicate Sunday through Saturday:
* To compile:
* CRTSQLRPGI ... DATFMT(*ISO)
D weekday s 10i 0
D duedate s d
D
C/exec sql
C+ select dayofweek(:duedate)
C+ into :weekday
C+ from qsys2/qsqptabl
C/end-exec
The second example produces a hex representation of a character string. If charvalue is ARITHMETIC, hexvalue becomes C1D9C9E3C8D4C5E3C9C3:
D hexvalue s 20a D charvalue s 10a D C/exec sql C+ select hex(:charvalue) C+ into :hexvalue C+ from qsys2/qsqptabl C/end-exec C
The third example uses the TRIM function to remove leading zeros from a number stored in a character variable. If NBR has the value 000000048.20, EDITEDNBR takes the value 48.20 and has a length of five bytes:
D editednbr s 20a varying D nbr s 12a D C/exec sql C+ select trim(leading '0' from :nbr) C+ into :editednbr C+ from qsys2/qsqptabl C/end-exec
IBM’s online SQL Reference for V5R2, “DB2 Universal Database for iSeries SQL Reference,” has a complete list of functions.
Thanks to Jeff and Yannick for sharing this technique.
— Ted
Sponsored By WORKSRIGHT SOFTWARE
|
|
CASS Certification
What is it? Why do you need it?
CASS stands for “Coding Accuracy Support System.” This is a test developed by the U.S. Postal Service to determine whether ZIP Code software can accurately assign ZIP Codes to mailing addresses. CASS-certified software is intended to improve the accuracy of carrier route, 5-digit ZIP, ZIP+4, and delivery point codes.
CASS certified means the software has passed the test. When you use CASS software to update and maintain your mailing file, then your mailing file becomes CASS certified. What does this mean to you?
CASS certification is the first step in qualifying for postage discounts. Look at the mail you receive at home and at work; you will see that a lot of it was mailed for less than the regular 37 cent rate.
Despite all the glitz and glamour of the Internet, email, and such, the wheels of America’s economy are lubricated by the ordinary envelope and the U.S. Postal Service.
If your company sends out a lot of mail, there is a good chance CASS certification can cut your postage expense. There are other steps you must perform to get these discounts, but CASS is the first step.
Your mail must be printed, sorted, and then packaged according to postal regulations. It takes some effort, but the potential savings make it worthwhile.
Exactly what happens when you process your mailing files with CASS software? The software breaks the address down into its individual elements: state, city, street name, and so forth. Then the information is compared to the national ZIP+4 database. If a match is found, the ZIP Code, ZIP+4, delivery point, and carrier route are assigned. Also the delivery line is standardized to comply with Postal Service preferences.
If an address can’t be matched, no action is taken. This has the positive effect of allowing you to identify addresses in your mailing files which possibly can’t be delivered, will be delayed in delivery, or at the very minimum need some minor correction to one of the address elements.
Using CASS certified software has many side benefits. You will be able to identify addresses that are potentially undeliverable. It has been estimated that as much as 30% of all advertising mail is never delivered.
That means the costs of postage, printing, paper, and overhead for undelivered mail are wasted. That means if you spend $100,000 on a direct mail campaign, as much as $30,000 could be completely wasted.
CASS software provides a number of intangible benefits. With CASS certification and bar coding there is a good likelihood that that your mail will be delivered sooner. If your invoices are delivered one day sooner, you may receive payment one day sooner.
If you CASS certify your mailing file, you can move on to the next step which is postal automation. This means applying a POSTNET bar code.
POSTNET is that little row of tall and short bars you see on your mail. POSTNET is a special bar code used by the Postal Service to allow automatic sorting machines to work.
It has been reported that bar code standard class (the old third class) mail gets delivered about as soon as first class, instead of the usual 2 or 3 weeks.
The bottom line is that using CASS software will help you keep your customers’ address information in much better shape than they would be otherwise. Your mail may be delivered sooner at less cost and that means more bottom-line dollars for your company.
If you would like more information about CASS and CASS software,
Are FedEx, United Parcel, and Airborne charges
Every bad address can result in a $5 to $10 charge. Even seemingly small addressing errors such as misspelled street names, or missing suite numbers can result in the same penalty as if the address were totally wrong.
Our PER/ZIP4 addressing matching software will match your addresses to the national ZIP+4 postal database. Based on the match PER/ZIP4, will update your addresses to postal standards. Our sophisticated address logic can add missing directionals, correct minor misspellings, and standardize the address format. If PER/ZIP4 can’t match the address, then you know that there is likely a serious error in the address that needs to be corrected.
PER/ZIP4 can be used in a batch mode to update an entire file or interactively to update individual address one at a time. We provide a search function so that you can search the national ZIP+4 database to verify a specific address.
Visit our Web site www.worksright.com for more information and to order a free, no-hassle, 30-day trial. Or call WorksRight Software, Inc., at 601-856-8337. |
