Re: Creating Views on Multiple Identical Tables
There could be one solution to this problem ( a looooooooooong shot),
but carries lot of conditions on its back. You could use the rowid(our
old friend) differences of the tables involved in the where condition,
again, again, provided you could arrive at a constant difference. (I
know this is insane, but hey! this is something).
Actually, getting a constant difference between the tables should not
be a problem, if you are populating there tables at a certain point of
time, without deletes. And provided the tables involved are not
fragmented.
Three tables (aa, bb, cc) with cols (aacol, bbcol, cccol)
rowids of table "aa" starts with 60257 and goes on ....
(hypothetical)
rowids of table "bb" starts with 20200 and goes on ....
(hypothetical)
rowids of table "cc" starts with 40005 and goes on ....
(hypothetical)
the view can be :
create view vue_all (col1, col2, col3)
as
select aacol, bbcol, cccol
from aa, bb, cc
where aa.rowid - bb.rowid = 40057
and aa.rowid - cc.rowid = 20252;
Vj - Jai boolo ganesh maharaj ki -- Jai
} 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.
}
} 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 |
} +-----------------------------------------------------------+