Selecting a PF with multiple members from SQL

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT DB2/400 and DB2 UDB When is the YES option for 'reuse deleted files' function the best choice? Monitoring members 'stuck' within a physical file on an EDI system Developing tables in a parent-child relationship in DB2 SQL server error message -321 Creating a host variable of the 'where in' statement in SQL Choose which column names are returned via ODBC when working with DB2 files Allow access to data from a stored procedure result set using COBOL or RPG Access path and an open data path differences Convert a numeric physical file to a character in SQL without leading zeros Inserting data from a CTE into a file in SQL iSeries SQL commands and statements Enhancing RPG with external SQL stored procedures Tracking data changes on IBM i with triggers Introduction to SQLRPGLE on IBM i: Making a report Making the most of RPG data handling on IBM i When is the YES option for 'reuse deleted files' function the best choice? Monitoring members 'stuck' within a physical file on an EDI system Creating a host variable of the 'where in' statement in SQL SQL server error message -321 Choose which column names are returned via ODBC when working with DB2 files Convert a numeric physical file to a character in SQL without leading zeros iSeries SQL commands and statements Research

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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;