From: | Dimitri Fontaine <dfontaine(at)hi-media(dot)com> |
---|---|
To: | Guy Deleeuw <G(dot)De_Leeuw(at)eurofer(dot)be> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: How to use an alias name in the current select |
Date: | 2010-03-24 19:09:18 |
Message-ID: | m2d3ytwn8h.fsf@hi-media.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi,
Guy Deleeuw <G(dot)De_Leeuw(at)eurofer(dot)be> writes:
> SELECT
> t_orders_articles.k_id AS k_id
> ,t_orders_articles.quantity AS order_qty
> ,(SELECT sum(quantity) FROM t_deliveries_articles WHERE article_id =
> t_orders_articles.k_id) AS delivery_qty
> , (t_orders_articles.quantity - delivery_qty) AS qty
> FROM t_orders_articles
> ......;
>
> How can I make that without having 2 select call ?
Put it as a relation in a subquery, or use WITH if using 8.4.
SELECT k_id, order_qty, quantity, delivery_qty,
quantity - delivery_qty as qty
FROM (
SELECT
t_orders_articles.k_id AS k_id
,t_orders_articles.quantity AS order_qty
,(SELECT sum(quantity) FROM t_deliveries_articles WHERE article_id =
t_orders_articles.k_id) AS delivery_qty
, t_orders_articles.quantity
FROM t_orders_articles
) as t;
Or
WITH t AS (
SELECT
t_orders_articles.k_id AS k_id
,t_orders_articles.quantity AS order_qty
,(SELECT sum(quantity) FROM t_deliveries_articles WHERE article_id =
t_orders_articles.k_id) AS delivery_qty
, t_orders_articles.quantity
FROM t_orders_articles
)
SELECT k_id, order_qty, quantity, delivery_qty,
quantity - delivery_qty as qty
FROM t;
Regards,
--
dim
From | Date | Subject | |
---|---|---|---|
Next Message | Ray Stell | 2010-03-24 20:54:44 | Re: 8.2.15 pitr/00000001.history |
Previous Message | Ray Stell | 2010-03-24 17:10:31 | 8.2.15 pitr/00000001.history |