By executing the above extended SQL statement, the physical file name can be remapped dynamically. This requires that the record structure of the new file be identical to that of the previous file. Works with any file-based data source, for example, VSAM, C-ISAM, Microfocus, POWERflex, DataFlex, and RMS.  When using {fn setfilename} multiple times in a query for the same table name such as the union example below, a table alias must be used.

 

Example1:

SELECT * FROM customer {fn setfilename customer, 'c:\customers\customer.dat'}

 

Example2:

SELECT * FROM RMS.dbo.customers_rms a {fn setfilename a ,'orders.dat'}

union all

SELECT * FROM RMS.dbo.customers_rms b {fn setfilename b ,'cnxdir:customer.dat'}

union all

SELECT * FROM RMS.dbo.customers_rms c {fn setfilename c ,'products.dat'}

union all

SELECT * FROM RMS.dbo.customers_rms d {fn setfilename d ,'customers.dat'}