Re: Creating Views on Multiple Identical Tables

> > } Jacob Salomon wrote: > } > } dhinkle@firstam.com (David J. Hinkle) wrote: > } > } |Is there a way to create a view on a group of tables that have > } |identical col. names? The tables contain data from the 4 quarters of > } |the year, ie: > } | > } | ba01_96q1 > } | ba01_96q2 > } | ba01_96q3 > } | ba01_96q4 etc... > } | > } |I need to create a view that contains the data from all the tables, > } |however the following statement errors out: > } | > } |create view ba01_all as select * from ba01_96q1, ba01_96q2, > } |ba01_96q3, ba01_96q4. > } > } [....] > } > } Now, what you really need is something like: > } > } create view ba01_all > } as select * from ba01_96q1 > } union > } select * from ba01_96q2 > } union > } select * from ba01_96q3 > } union > } select * from ba01_96q4 > } > } Alas, as far as I know, a view on a union is still illegal. Siighhhh L > } > } I have heard rumors that the a view on a union is legal in the SQL-92 > } standard and that the Informix engines of release 7.2+ conform to SQL-92 > } syntax. However, I have no first hand knowledge of this and don't mind > } trolling for an answer. > } > } |Is there a way to do this, or am I out of luck? > } > } Sounds to me like if you are determined to combine the tables with a > } view, you are probably out of luck, unless the aforementioned rumor pans > } out. > } > } However=85. > } > } IMO, you got it backwards. The *table* ba01_all should cover all > } quarters, all years. If your application demands quarterly tables, use > } a separate view for each quarter: > } > } create view ba01_96q1 > } as select * from ba01_all > } where some_date_column between "01/01/96" and "03/31/96" > } -- = > } > } -- Jake (In pursuit of undomesticated aquatic avians) > } = > } > } +-----------------------------------------------------------+ > } | Impeccable Logic: A thought process which successfully | > } | resists chicken bites | > } +-----------------------------------------------------------+ I agree with Jake's last point. However, tables being as there are, you can perhaps use a stored procedure with appropriate 'unionised' select, and pass parts of 'where' clause as parameters. Of course, the functionality is far from equivalent (as procedure isn't handled as a table, cannot be used as part of other select etc), but if you need it only in a report or something, it might work. -- Dragi "Bonzi" Raos 4-MATE Information Engineering (http://www.4mate.hr) Zagreb, Croatia