*N or SQL5001 errors with AS/400

Whenever you get an SQL5001 error, it is usually one of two things. In your case, I know it is not the first of these, but lets include the information for review. If you get this error and see *N as the qualifier, you need to check that you have a host database name declared on your system. You can check this by using the Work Relations 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 command DSPNETA and get the value for your Default Local Location. This 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 this should resolve the problem.

The second reason for this error is special characters in the column or table names of your application. The AS/400 allows you to use the characters $@# 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 spec, so it does not expect to see these characters in your table or column names. The only cure is to crate an alias or view for the offending table. Here is an example:

Say we have a table with the following offending columns:

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

In order to make this table friendly to Crystal, I recommend that you create an SQL view of the data. A view is like a logical file, expect it has no data in it. When you attempt to access the view, the underlying SQL statement is executed by the AS/400 and the data is returned to the application as if the data is in a physical file. Here is a create view statement that makes the above table SQL happy:

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

Once the above statement is executed, (you can do this via STRSQL, place the statement in a source physical file and use RUNSQLSTM, use Client Access Express or use a tool like SQLThing), 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.