Using SQL on the AS/400 is a very fast and useful feature for finding information quickly and efficiently. However, what do you do when you want to retrieve information from a file which contains a period (.) in its name? For example, if you try to select file AP.MASTER through SQL, you will get error message SQL0104 ("Token . was not valid..."). I called IBM's Level 1 support and asked how to get around this. They told me to execute the Override with Database File (OVRDBF) command to override the nonstandard file name to a name that is standard; and then, use the standard name in my SQL statement.

Determined to find an easier way, I discovered that you can save a lot of keystrokes and time by simply putting the file name in quotes, (e.g., "AP.MASTER") in your SQL statement. This method still allows you to prompt the SELECT fields line, plus it saves you time and aggravation by eliminating the need for the OVRDBF command for each file. The best thing about using the quotes is that you won't have to exit and restart SQL to do an override if you decide to search for records in different files.

5 illustrates the SQL statement I used for the AP.MASTER file. If you have the same file name in two different libraries you may have to qualify the file name. For example, type QS36F/"AP.MASTER" for the LIBRARY/FILE that you want to select on the FROM line. Type the file name in capitals or SQL will not find the file.

Figure 5 illustrates the SQL statement I used for the AP.MASTER file. If you have the same file name in two different libraries you may have to qualify the file name. For example, type QS36F/"AP.MASTER" for the LIBRARY/FILE that you want to select on the FROM line. Type the file name in capitals or SQL will not find the file.

Nonstandard File Names and SQL

Figure 5 Referencing Nonstandard File Names in SQL

 SELECT * FROM "AP.MASTER" WHERE PAYDTE = 940101