RE: Summary view of arbitrary number of identically-named-and-for matted physical files in separate libraries

Do you want a single summary of all files, or a summary line from each file?

Lib A, summary is 100
Lib B, summary is 200

Or

Summary is 300

I think you can get the first with something like this in SQL (unchecked
syntax)

Create view summary as
Select cast('Lib A' as char(10)), sum(column1) from liba/filea
Union 
Select cast('Lib A' as char(10)), sum(column1) from libb/filea

To achieve the second result, I think you need the above with

Select sum(column1) from summary

Or

With summary as (
Select cast('Lib A' as char(10)), sum(column1) from liba/filea
Union 
Select cast('Lib A' as char(10)), sum(column1) from libb/filea
)
Select sum(column1) from summary

Not sure about "dynamic"; when the same file is added to another library,
you would need to update the view.

HTH,
Loyd


Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: James H H Lampert [mailto:jamesl@xxxxxxxxxxx] 
Sent: Tuesday, September 21, 2004 19:44
To: midrange-l@xxxxxxxxxxxx
Subject: Summary view of arbitrary number of identically-named-and-formatted
physical files in separate libraries

Is there a halfway-simple way of generating a summary view across a bunch
of identically-named-and-formatted files in different libraries? Maybe
some trick involving SQL? DDS? OPNQRYF?

--
JHHL