Scenario

Let's say you have a file named CUSTMAST in library CUSLIB on your iSeries. 
 
This file has multiple data members for each company:
 
MBR0001 - Company 1 data 
MBR0002 - Company 2 data
MBR0003 - Company 3 data 
 
How would you be able to read data from each member since SQL/400 does not allow access to the individual file members?
 
You would need to:
  1. Create an ALIAS for each data member so they can be referred to separately.

    Example SQL statements:

    CREATE ALIAS CUSLIB/MBR0001_ALIAS FOR CUSLIB/CUSTMAST (MBR0001)      
    CREATE ALIAS CUSLIB/MBR0002_ALIAS FOR CUSLIB/CUSTMAST (MBR0002)      
    CREATE ALIAS CUSLIB/MBR0003_ALIAS FOR CUSLIB/CUSTMAST (MBR0003)

    Creating the SQL Aliases via the FTSQL command in the RJSTRAN library:
     
    RJSTRAN/FTSQL SQLSTM('CREATE ALIAS CUSLIB/MBR0001_ALIAS FOR CUSLIB/CUSTMAST (MBR0001)')
    RJSTRAN/FTSQL SQLSTM('CREATE ALIAS CUSLIB/MBR0002_ALIAS FOR CUSLIB/CUSTMAST (MBR0002)')
    RJSTRAN/FTSQL SQLSTM('CREATE ALIAS CUSLIB/MBR0003_ALIAS FOR CUSLIB/CUSTMAST (MBR0003)')
     
  2. Now when SQL statements are run against a particular member, use a new ALIAS.

    Examples of using an ALIAS with the FTSQL statement for selecting records:
     
    FTSQL SQLSTM('SELECT * FROM CUSLIB/MBR0001_ALIAS')                        
    FTSQL SQLSTM('SELECT * FROM CUSLIB/MBR0002_ALIAS')                        
    FTSQL SQLSTM('SELECT * FROM CUSLIB/MBR0003_ALIAS')

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016