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

Apparently, ORDER BY is allowed in CREATE TABLE, but not CREATE VIEW.  As
for the syntax, ORDER BY 3,2,4 would mean first order the file by 3rd field,
then 2nd and finally 4th field.

I could not find IBM document outlining physical limit on the number of
UNIONs in a single statement.  I think it may have been raised to 256 in
recent OS releases, but it would warrant testing to confirm that.

In any case, this solution would create another physical file, and Jim could
probably do that using CPYF if he wanted to.  
SQL View would not copy the data, but would not be keyed and thus not RPG
friendly (unless using it in sequential order).
Oh, well...

Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CWilt@xxxxxxxxxxxx
Sent: Wednesday, September 22, 2004 11:33 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Summary view of arbitrary number
ofidentically-named-and-formatted 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
--