Get started Bring yourself up to speed with our introductory content.

Creating an SQL command to view two files as one

Expert Kent Milligan explains an SQL statement that will allow two separate transaction files to be viewed as a single file.

We have a transaction file whose records get archived to another file as they complete their process. The file names differ but the field names are the same. Is there a way that we can view the two files as if they are one?

The history file is large so we would prefer to avoid creating a third file and copying the records from each of the first two files into it.

As long as the format of the two files is the same, you can create an SQL view that UNIONS them together, thus making them appear as one file.

For example, if your transaction file is named TRANSFILE and your archived file is HISTTRANS, the following SQL statement would create the appropriate view:

CREATE VIEW view all_trans AS
(SELECT * from histtrans
SELECT * FROM transfile)

This view can be used for both native record level access and SQL access. Bear in mind that SQL views are equivalent to non-keyed logical files.

Dig Deeper on iSeries SQL commands and statements

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.