Re: Referncing a calculated column in a select?

Hello.

At Thu, 12 Sep 2019 23:16:01 +0200, "Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> wrote in <20190912211601(dot)GA3842(at)hjp(dot)at>
> 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.
..
> What you can do is nest views:

Doesn't subquery work?

SELECT x, y, z
FROM (SELECT f * 3 AS x, f AS y, g + 2 AS z
FROM (SELECT a + 3 AS f, b + c AS g
FROM t) AS t1
) AS t2;

t2 uses f in two columns, where f is calculated from t.a.
Or CTE (WITH clause) might look better.

WITH t1 AS (SELECT a + 3 AS f, b + c AS g FROM t),
t2 AS (SELECT f * 3 AS x, f AS y, g + 2 AS z FROM t1)
SELECT x, y, z FROM t2;

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center