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

1a)  the a. and the b. are basically just declaring shortcuts, or renames 
for the files.  Notice the a after file1 and the b after file2.  It's how 
you qualify fields to their files.

1b)  The cast clause creates a new field in the file.  The field will be 
the library name.  We use cast so we can tell the field type.  We don't 
want SQL to assume a short size if the library name is less than 10 
characters.

2)  The order by 3 says order by the third field.  The first is the file 
name field we created therefore the third field would be field2

3)  Just add more Union statements
> 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
>     UNION ALL
>   SELECT CAST('library3' AS CHAR(10)), c.* FROM library3/file1 c
>   ORDER BY 3)
> WITH DATA

4)  Yes it is a new permanent file and not a logical file.

5)  Does SQL get over the 32 file limitation?

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"James H H Lampert" <jamesl@xxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/22/2004 11:11 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
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


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.