Re: Creating Views on Multiple Identical Tables

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. The above failed a semantic check because you were creating a view in which each column name is used 3 times. You can get around this by using the syntax: create view ba01_all (q1_col1, q1_col2, q1_col3=85.., = q2_col1, q2_col2, q2_col3.. q3_col1, q3_col2, q3_col3.. q4_col1, q4_col2, q4_col3) as select * from ba01_96q1, ba01_96q2, ba01_96q3, ba01_96q4 However, this produces a very wide view with 4 times as many columns as the data it is meant to represent. Also, if you try running that query on its own you will get a Cartesian product of 4 sets. As you [ought to] know, a Cartesian product is almost entirely garbage, with a fraction of the data (like .0001%) having some actual meaning. Therefore, the corresponding view will equally useless. Since the data in these tables is essentially parallel (is this a real technical term in database theory?) data, there is no really good way to join them all in a chain of 1-1 relations. Try to establish a relation between the orders of Q1 and those from Q2. When you realize how this is not "well posed" (now that IS a term from mathematics, at least ;-) do it again with 4 tables. It can't really be done. 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 | +-----------------------------------------------------------+