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

> -----Original Message-----
> From: James H H Lampert [mailto:jamesl@xxxxxxxxxxx]
> Sent: Wednesday, September 22, 2004 12:11 PM
> To: midrange-l@xxxxxxxxxxxx
> 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,

Cast simply converts one data type to another.  In Elvis' post, the literal
'library1' which would normally be treated as variable char by SQL is
converted to fixed length.  By casting it, he prevents the table from having
a varchar first field.  

"a" and "b" correlation name given to the tables.  Basically, an easy way to
qualify the table.  Say you had a table named
"ORDERS_WHICH_HAVE_NOT_BEEN_PROCESSED".  You wouldn't want to type that too
many times, so you can say:

select a.*, b.*
from ORDERS_WHICH_HAVE_NOT_BEEN_PROCESSED a, SOME_OTHER_TABLE b
where a.field1 = b.field1

note that both tables have a field of the same name (field1) so you need to
qualify them in order to use them.  I'll often use 'a', 'b', or maybe 'itm',
'hdr', 'dtl' as correlation names.  Finially, "a.*" simply means all fields
in "a".
 

> 2) explain what the "order by 3" clause does, and

Order by orders a results set.  In this case, I don't believe it will do
anything.  In fact I'm not sure SQL will even allow it to be used in the
statement.

> 3) show how this could be expanded to cover more than 2 libraries?

just add UNION ALL and the select for the next library.  Union <ALL> just
joins the results sets of the select statments.

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

Yep, you're creating a physical table.  You could do a view in the same
manner, but you won't be able to add an index to the new view.

If you plan to only use SQL on the view, it's ok.  But if you want somthing
you can do random RPG I/O to it won't work.

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


In that case you're pretty much screwed as even an SQL view can only be
based on 32 different files.


Have you given any thought to the idea mentioned in my first post,
consolidating all the phusicals into a single physical?  That's pretty much
your only option.

Charles