TechTip: Handle Multiple Members Within SQL

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times
When I read Jeff Olen's TechTip on handling multiple-member files within RPG code without resorting to the traditional OVRDBF command, I realized that a lot of people may not know how to handle that situation within SQL.

If you want to identify a particular member within a file, use the following SQL command:

CREATE ALIAS your-alias FOR your-library/your-file (your-member)

When you issue a command such as the following select statement...

SELECT * FROM your-alias.

...the data is read from the specific member associated with the alias. Multiple members are not allowed for tables created through SQL, but if you are accessing data in non-SQL tables, this works fine.

The alias is actually implemented as a Distributed Data Management (DDM) file that points at the *LOCAL database.

Kevin Forsythe has over 19 years of experience working with the iSeries platform and its predecessors. He has been a member of the DMC team for the past nine years. Kevin's primary responsibility is providing iSeries education, but he also provides customers with project management, system design, analysis, and technical construction. In addition to his technical skills (RPG IV, CL, OS/400, SQL, FTP, Query, VB, Net.Data), Kevin possesses the ability to communicate new and complex concepts to his students. He has been the primary instructor for DMC's iSeries-based AS/Credentials training courses since 1997 and has authored courses such as Advanced ILE, SQL, Embedded SQL, Operations Navigator, and Intro to WebSphere Studio. An award-winning speaker, he has spoken at every COMMON Conference since the spring of 2000.