A Room with a View

Hey, Howard:

I haven't messed with views much until now, but I came across a good use for one today. I created it, and it was good. But then I noticed that the logical I was hoping to replace with the view was sorted in ascending order by a couple of fields, so I added an ORDER BY clause. The system let me know in no uncertain terms that I couldn't do that.

For today's purpose, the order really is not important, but it set me to thinking--unless I'm missing something, which is likely enough--that views might be better than logicals for some applications. But one of the main uses of a logical is to read the physical in a different order.

Is there a way to create a view in a sorted order, or does this concept fall outside the world view of SQL, since if you use the view in a SELECT statement, you'd do the sorting there?

--David

Dear David:

Yes, there is a limitation with views, in that you cannot specify an ORDER BY clause in the view. This is not likely to change, since the SQL-92 specification does not permit the ORDER BY clause in view definitions.

However, let's say you made a view and wanted to use it in an RPG program as a replacement for a logical that has order. You would need to use embedded SQL and place an ORDER BY statement at the end of the SELECT. So, yes, you're right. The only way to order a view is to use it in a SELECT statement.

Views are good alternatives to logicals, but there is that limitation. I had a customer with a physical file that had over 60 logicals. We looked at the logicals and some were being accessed only once a month. So we changed the programs that used those logicals to use embedded SQL, and killed the logical. We got rid of a lot of logicals that way and replaced them with views or embedded SQL. Getting rid of the logicals translated into less disk maintenance when making changes to the physical file, which maps to more capacity for other operations.

Another use for SQL views is to rename fields to names that are more acceptable to modern applications. For example, Crystal Reports gets very unhappy when a field name includes an @, $, or #. If you need to access a table with these field names, try creating a view. Here is an example.

Given the following physical file PARTS:

Field Name   Data Type  Length
----------   ---------  ------
PART#        CHAR       10
PRICE$       DECIMAL    9,2
ONHAND#      DECIMAL    10,0

You could issue the following Create View statement to make a palatable representation of this table for products like Crystal:

CREATE VIEW PALATABLE_PARTS
(PART_ID,  
PART_PRICE, 
ON_HAND,
WORTH)
AS
SELECT PART#, PRICE$, 
       ONHAND# , ONHAND#*PRICE$
       FROM PARTS

Now you have a view with names that will not choke PC-based products.

Finally, you can use views to translate short obfuscated AS/400 field names into a human readable form for use with products like Crystal and Access. One of my customers has field names like ARN015 for Company Number and ARNM02 for Salesperson Name. When you create a view, you can rename the fields to nice pretty strings like COMPANY_NUMBER or SALESPERSON_NAME. Then users can access the view with third-party tools and not keep calling the programmers asking, "What's the name of the company number field again?"

Howard F. Arner, Jr., is the author of iSeries and AS/400 SQL at Work, which you can purchase from his Web site, www.sqlthing.com, if you want to know more about SQL views. Howard's company, Client Server Development, is based in Jacksonville, Florida, and specializes in development tools like SQLThing Enterprise Edition and consulting for DB2 on the iSeries.

Sponsored By COMMON

Get the IT training you need by attending COMMON Users Group's Spring 2003 IT Education Conference and Expo, March 9 - 13, in Indianapolis. Choose from hundreds of sessions and labs covering the widest range of industry topics, including RPG IV, LPAR, WebSphere, and High Availability. COMMON is the best value in IT education, so don't miss out! Click and visit www.common.org for details!