Re: Creating Views on Multiple Identical Tables

Joe Lumbley wrote: --- SNIP --- > Ahhhhhhh, but I spoke too soon, there's another way to do it, maybe. > > Given two tables, table1(name char(10)), table2(name char10), you > can make it work using a (slap my hands!!!!) disjoint join. > > create view awful_view as> select table1.name firstname, table2.name secondname > from table1,table2; > > This depends upon whether or not you can live with the disjoint join > on table1,table2. If they're small, it won't generate too big a > result set. If you have a field that you can join the two tables on, > you'll avoid the disjoint join. Joe, I believe I addressed this issue already in my post, wherein I made reference to "Cartesian products". Since the tables involved are quite identical in layout and purpose (the only difference being the quarter), there is nothing to join on. And even with small tables of, say, 100 rows each, the "disjoint join" results in a cartesian product of 10,000 rows, of which at most 100 are useful. And each row (continuing your example) has twice as wide as the original rows. And, of course, they would need to be named in the "create view" command, because the column names are still identical across the tow tables. I addressed this as well; the error message was the reason for his original post. What this man needs is a view that mimics the 200 useful rows combining both (or all) quarters. (And it's certainly *not* 200; more likely thousands.) My original suggestion was that he define the quarterly tables as views against a monolithic table that covers all seasons. Another brain-fart: Leave the quarterly tables as they are but put the UNION'ed select in a stored procedure and use a scroll cursor to get all rows from the procedure. This kind of data tends to be static once it is in the quarter past so stale data is a smaller concern than it might otherwise be. -- -- Jake (Gets a perverse kick from challenging established gurus) +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+