From: Brian Kautz To: All

I just started playing with SQL using the Execute SQL Statement (EXCSQLSTM) command (see "The EXCSQLSTM Utility," MC, December 1994). How does SQL on the AS/400 handle files with multiple members? When I run the following command, I get only the data from the first member of the file.

SELECT * FROM MYLIB/MYFILE

How do I search over all members at once? I tried the Override Database File (OVRDBF) command, but I got an SQL error saying you could not use OVRDBF with *ALL for the members. Does SQL on the AS/400 support selecting an individual member or selecting all members at once? Is the concept of multiple file members in a file universal or unique to the AS/400 environment?

From: Chris Rowell To: Brian Kautz

SQL supports the selection of individual members though the MBR parameter on the OVRDBF command. As you have already seen, you can't use MBR(*ALL) to process multiple members.

From: Richard Shaler To: Brian Kautz

The concept of multiple member files is a unique feature of the AS/400 database system and unknown to SQL. You could easily copy all members of your multiple member file to a single member file with a single Copy File (CPYF) command. Then run your SQL statement against the newly created file.

 CPYF FROMFILE(multi-mbr-file) + TOFILE(file-name) + FROMMBR(*ALL) + CRTFILE(*YES) 

If the file was very large, you probably wouldn't want to use this method.