On 2019-09-12 15:35:56 -0500, Ron wrote: > On 9/12/19 2:23 PM, stan wrote: > > I am creating some views, that have columns with fairly complex calculations > > in them. The I want to do further calculations using the result of this > > calculation. Right now, I am just duplicating the first calculation in the > > select fro the 2nd calculated column. There must be a batter way to do > > this, right? > > I must be misunderstanding you, because you can just do more of what you're > doing now. > > Here's an example of doing some calculations in the creation of the view, > and then more calculations when selecting from the view:
I think he wants to refer to other columns in the view. Something like create or replace view v as select a, b, abs(b - a) as abserror, case when abs(a) > abs(b) then abs(a) else abs(b) end as mag, abserror / mag as relerror from t; Except that this doesn't work. What you can do is nest views: create or replace view v as select a, b, abs(b - a) as abserror, case when abs(a) > abs(b) then abs(a) else abs(b) end as mag from t; create or replace view v2 as select a, b, abserror, mag, abserror / mag as relerror from v; wds=> select * from v2; ╔══════════════╤═════════╤══════════════╤══════════════╤═══════════════════╗ ║ a │ b │ abserror │ mag │ relerror ║ ╟──────────────┼─────────┼──────────────┼──────────────┼───────────────────╢ ║ 2 │ 3 │ 1 │ 3 │ 0.333333333333333 ║ ║ -2 │ 3 │ 5 │ 3 │ 1.66666666666667 ║ ║ 3.1415926536 │ 2.71828 │ 0.4233126536 │ 3.1415926536 │ 0.134744602587137 ║ ╚══════════════╧═════════╧══════════════╧══════════════╧═══════════════════╝ (3 rows) No idea whether this is more or less efficient than writing the whole formula for each column. hp -- _ | Peter J. Holzer | we build much bigger, better disasters now |_|_) | | because we have much more sophisticated | | | h...@hjp.at | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
signature.asc
Description: PGP signature