Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to use an alias name in the current select

How to use an alias name in the current select

From
Guy Deleeuw
Date:
Hello

I have :
    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
        ......;

But it's not working I receive the error :
ERROR : The column delivery_qty does not exist.

How can I make that without having 2 select call ?

Regards
Guy

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

From
Dimitri Fontaine
Date:
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

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

From
Guy Deleeuw
Date:
Hello Dimitri,

Many thanks for your answers

Regards
Guy

Le 24/03/10 20:09, Dimitri Fontaine a écrit :
> 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,
>