Skip site navigation (1) Skip section navigation (2)

Using expression names in subsequent calculation

From: Tony Theodore <tony(dot)theodore(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Using expression names in subsequent calculation
Date: 2011-09-11 06:13:50
Message-ID: CAJFv53qhOROQ8ED=QZEGq6cn_Xm86bfZGrUe+SkHssp+QF9=Yg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,

I'm migrating an application from Access, and having great fun so far,
but I'm running into problems with some queries. What I'm trying to do
boils down to this:

SELECT 10 AS qty, 0.5 AS price, 0.1 AS tax_rate, qty * price AS val,
val * tax_rate AS tax_amount, val + tax_amount as total

but get a "column qty does not exist" error.

In a real query - qty, price and tax_rate would come from actual
columns, but then the error is on the "val" column. I'd like to use
the name of the expression rather than repeat the calculation.

Near the bottom of the SELECT docs, there's a note about namespaces
that explains why this won't work, but I'm wondering how to go about
this type of query.

So far I've come up with:

WITH order_details AS (SELECT 10 AS qty, 0.5 AS price, 0.1 AS tax_rate),
order_values AS (SELECT qty * price AS val from order_details),
order_taxes AS (SELECT val * tax_rate AS tax_amount FROM
order_details, order_values)
SELECT qty, price, tax_rate, val, tax_amount, val + tax_amount as
total FROM order_details, order_values, order_taxes

However, that looks at lot more complicated than simply repeating the
calculations ;)

What's the best way go about calculations like this that build upon
previous results?

Thanks,

Tony

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2011-09-11 16:00:34
Subject: Re: Using expression names in subsequent calculation
Previous:From: Tim DunphyDate: 2011-09-09 21:00:01
Subject: cant' start psql ipv6 error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group