Re: [ADMIN] How to use an alias name in the current select

[Guy Deleeuw]

Hello Dimitri,

Many thanks for your answers

Regards
Guy

Le 24/03/10 20:09, Dimitri Fontaine a écrit :

Hi,

Guy Deleeuw<g.de_le...@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,

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin