Hey Ted :

I have a PTF that is showing on my server as damaged. I have tried several ways to fix this, including re-downloading the PTF and using the Delete PTF (DLTPTF) command and the Remove Program Temporary Fix (RMVPTF) command, and I have been unsuccessful. Can you assist me with this problem?

I sure can. The PTF must be reloaded with the Load Program Temporary Fix (LODPTF) command and then the RMVPTF command can be used to remove it. Run LODPTF from the media that you used to originally load the PTF. If you no longer have that media, download the PTF again. Once the PTF has been loaded, remove it. If everything on your system appears to be running properly, then wait until after your next IPL to reload the PTF. The IPL will reset the pointers to the objects contained in the PTF. If you experience system problems, IPL as soon as possible and then reload the PTF. If you have a damaged PTF, you may also have damaged objects. You might want to run the Reclaim Storage (RCLSTG) command as soon as possible.

Hey Howard:

I just got a copy of Crystal Reports, and am really enjoying the product. However, one of the files in the QS36F library on my AS/400 doesn’t seem to work with Crystal. I can add the file to a report, but when I use fields from that file and attempt to preview the report, I get an SQL5001 error: column qualifier or table undefined. What should I do?

Always consult with a Midrange Guru, as you have wisely done.

Whenever you get an SQL5001 error, it is usually due to one of two reasons. In your case, I know it is not the first of these situations, but I'll include the information for review. If you get this error and see *N as the qualifier, check that you have a host database name declared on your system. You can check this by using the Work With Relational Database Directory Entries (WRKRDBDIRE) command. If you do not have an entry where the remote location reads *LOCAL, then you need to create one. Execute the Display Network Attributes (DSPNETA) command to get the value for your Default Local Location. This value is the name that you should use as your host database name. Next, execute the WRKRDBDIRE command to see the relational database directory entries. Use option 1 to add an entry using the name found in the DSPNETA command. Set the address of the system to *LOCAL and choose *SNA for the type. After IPL, you should no longer see *N at the front of your queries and the problem should be resolved.

The second reason for receiving this error is caused by special characters in the column or table names of your application. OS/400 allows you to use the dollar sign ($), the at symbol (@), the pound sign (#), and the period (.) in table and column names. However, the SQL specification only allows table and column names to be composed using letters, numbers, and the underscore (_) character. Crystal Reports is designed to the SQL specification, so it does not expect to see the OS/400-allowed characters in your table or column names. Crystal will generally get along with the $ and # characters, but it really doesn’t like the @ character in a column name. The only cure is to create an alias or view for the offending column or table, as shown in the following example:

QS36F/PARTS
                   PART#      Character 10
                   DESCR      Character 30
                   PRICE@1  Packed               10,2

In order to make this table friendly to Crystal, create an SQL view of the data. A view is like a logical file, except it contains no data. When you attempt to access the view, the underlying SQL statement is executed by an AS/400 or iSeries server and the data is returned to the application as if the data is in a physical file. The following example is a create view statement that makes the table Crystal-happy:

CREATE VIEW QS36F/MYPARTSVIEW AS
                   SELECT PART# AS PARTID
                                     DESCR AS DESCRIPTION
                                     PRICE@1 AS PART_PRICE
                   FROM QS36F/PARTS

Once the above statement is executed, you will have a view called MYPARTSVIEW in your QS36F library that Crystal Reports and other SQL/ODBC/ADO-based products can access with impunity. You can execute the above statement in several ways:

* Use the Start SQL Interactive Session (STRSQL) command

* Place the statement in a source physical file and use the Run SQL Statement (RUNSQLSTM) command

* Use Client Access Express; or

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.