Re: How to use an alias name in the current select - Mailing list pgsql-admin

Hi,

Guy Deleeuw <G.De_Leeuw@eurofer.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