RE: Summary view of arbitrary number ofidentically-named-and-formatted physical files in separate libraries

Elvis suggested:

> Jim, I may be off base here, but here is what I envision might work with
> SQL on V5R2:
>
> CREATE TABLE qgpl/t1 (library,field1,field2,field3) AS (
>   SELECT CAST('library1' AS CHAR(10)), a.* FROM library1/file1 a
>     UNION ALL
>   SELECT CAST('library2' AS CHAR(10)), b.* FROM library2/file1 b
>   ORDER BY 3)
> WITH DATA
>
> This will obviously need tweaking to work for your situation.
> You probably don't need the ORDER BY clause, if you follow this CREATE
> with ALTER TABLE and add a primary key that fits your needs.
> Key to this solution is UNION ALL feature of SQL.

Interesting. I think you may have something. At this point, I'm still very
much an SQL novice (which is to say, I'm still puzzling over some of the
examples in "SQL for Dummies"), so could you please
1) explain what the "cast" clauses and the references to "a" and "b" do,
2) explain what the "order by 3" clause does, and
3) show how this could be expanded to cover more than 2 libraries?

Also, with a "CREATE TABLE" am I not pouring the data into a new physical
file? It would be more useful to simply have a persistent view.

Oh, and as to generating any sort of logical through DDS, I was thinking
about that myself, but that would give me a brick wall at 32 based-on
physicals, and while customer installations are unlikely to have anywhere
near that many, our own installation is already up to nearly 50.

--
JHHL