I have a table with, say, a dozen fields and I want to end up with a view with
around 50 fields, calculated from the original dozen.
So it would be something like this:
CREATE VIEW final AS SELECT x, y, z, (x + y) as a, (y + z) as b, (x + y) * z
as c, (y + z) * x as d, (x + y) * z + x as e, (y + z) * x + x as f FROM my_table;
except my expressions are longer and more complicated. However, my expressions
do have similar dependencies and redundancies.
My question is what is a good way of dealing with this? I was going to do
something like
CREATE VIEW one AS SELECTid, (x + y) as a, (y + z) as b, FROM my_table;
CREATE VIEW two AS SELECTm.id, o.a * m.z as c, o.b * m.x as d, FROM my_table m, one o WHERE
m.id= o.id;
etc. but I'll end up with a lot of levels and joins going this route which I
expect will be pretty slow.
Can someone recommend anything to me? Should I be approaching this from a
completely different angle i.e other than views?
I'm pretty new at this so any pointers will be appreciated.
--Rick