Midrange Guru - OS/400 Edition

OS/400 Edition
Volume 2, Number 15 - February 27, 2002

More than One Way to Skin a Cat

Hey, Howard:

Perhaps I misunderstood the question, but you can use the ALIAS keyword in DDS to get more-descriptive field names for Crystal Reports.

-- Dave

You are correct, Dave! This just goes to show that there is always more than one way to do something on the iSeries. Using the DDS ALIAS keyword, you can specify alternate field names that will be used by ODBC, JDBC, and SQL against a physical file without having to create a view over the physical file.

However, let me point out some advantages of creating a view.

A view could incorporate fewer columns than that original physical file. This might be good if you want to limit user access to some of the columns, like maybe Social Security numbers in an employee file or salaries/pay rates.

A view can contain derived mathematical columns or incorporate values returned from SQL scalar functions. For example, I might want a column in my view to represent the day of the week of an event in the database and the month in which the event occurred, in addition to the date itself. The following statement contains derived columns that are not possible in DDS:

CREATE VIEW USERDATA 
  (SALE_DAY_OF_WEEK, SALE_MONTH, SALE_YEAR,
   COST, SELLCOST, GROSS_PROFIT,
   PROFIT_PERCENT)
AS
SELECT DAYOFWEEK(MYDATE),  MONTH(MYDATE), YEAR(MYDATE),
   COST, SELLCOST, COST-SELLCOST,
   DECIMAL((COST-SELLCOST)/COST*100,5,2)
FROM ITEMSALES;

Finally, when you create a view, the field names can be up to 128 characters whereas with an ALIAS in DDS, the field names can only be 30 characters. Now, 30 characters should be sufficient for most mortals, but if you are doing views for the super-best-friends you may need all 128 characters. (Please excuse the South Park Reference.)

But your point is well taken, Dave. For many applications, SQL may be overkill. It may be sufficient (and maybe easier) in many cases to add ALIAS keywords to physical file DDS and execute the Change Physical File (CHGPF) command in order to get longer field names.

-- Howard

Howard F. Arner, Jr. is the Vice President of Client Server Development, Inc. in Jacksonville, Florida, and is the author of iSeries and AS/400 SQL at Work. You can purchase a copy of SQL at Work from http://www.sqlthing.com/books.

Sponsored By
WORKSRIGHT SOFTWARE INC.

CASS Certification

What is it?

Why do you need it?

CASS stands for Coding Accuracy Support System. This is a test developed by the US 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, 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 34-cent rate.

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.

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 percent of all advertising mail is never delivered.

That means the costs of postage, printing, paper, and overhead for the undelivered mail are wasted. 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 barcoding 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.

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 visit our Web site or contact WorksRight Software, Inc. at 601-856-8337.

THIS ISSUE
SPONSORED BY:

WorksRight Software, Inc.

JACADA LTD.

BACK ISSUES

TABLE OF CONTENTS

More than One Way to Skin a Cat

Page 1 of X

Reader Feedback and Insights: Status Messages May Degrade Performance

	Newsletters \| Subscribe \| Advertise \| About Us \| Contact \| Search \| Home
	Last Updated: 2/27/02 Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.