How can I select a physical file (PF) with multiple members on AS/400 from Windows SQL?
The simplest technique is to create an alias using SQL for each member that you want to access with SQL. Here's an example of creating aliases.
CREATE DBLIB/AM1 FOR DBLIB/FILE(M1)
CREATE DBLIB/AM2 FOR DBLIB/FILE(M2)
Once the SQL ALIAS is created, any SQL interface can reference the alias name just like a table name.
The SQL alias object requires no maintenance, so creating the aliases should be a one-time operation. There's no overhead leaving the alias objects around.
Editor's note: We received a follow-up question to this question and answer:
First: The right statement is:
Create ALIAS DBLIB/AM1 FOR DBLIB/FILE(M1)
Second: You wrote, "Once the SQL ALIAS is created, any SQL interface can reference the alias name just like a table name." But if I have to use information from this member with information from other files, normaly I use: Create view myView as... That is not possible with alias. Is there another way to use a member in a view?
Kent responded with this:
You are correct, I did omit the alias keyword from my example. And an alias object cannot be referenced in a view definition.
One way to encapsulate an alias reference and then embed into a view definition is create a user-defined table function (UDTF):
CREATE FUNCTION aliasudtf() RETURNS TABLE(col2 char(6), col3 int) LANGUAGE SQL NO EXTERNAL ACTION DETERMINISTIC DISALLOW PARALLEL BEGIN Return ( select c2,c3 from alias1 union all select c2,c3 from alias2); END;
Here's an example of how to reference a UDTF on a SELECT statement. UDTF references are allowed when creating views:
SELECT col2 FROM TABLE( aliasudtf() ) x;
Dig Deeper on iSeries SQL commands and statements
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading