Q
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
UNION
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.

This was last published in October 2007

Dig Deeper on iSeries SQL commands and statements

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close