Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 79666632636 for ; Thu, 25 Mar 2010 04:19:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 48129-02 for ; Thu, 25 Mar 2010 07:18:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from hosting.mactelecom.net (hosting.mactelecom.net [217.64.240.243]) by mail.postgresql.org (Postfix) with ESMTP id D88DA632517 for ; Thu, 25 Mar 2010 04:18:55 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by hosting.mactelecom.net (Postfix) with ESMTP id DA5CB16614A; Thu, 25 Mar 2010 07:18:52 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at BRUBLUcs02.mactelecom.local Received: from hosting.mactelecom.net ([127.0.0.1]) by localhost (BRUBLUcs02.mactelecom.local [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id bzOW1r9eeXz0; Thu, 25 Mar 2010 07:18:52 +0000 (UTC) Received-SPF: none (eurofer.be: No applicable sender policy available) receiver=BRUBLUcs02.mactelecom.local; identity=mfrom; envelope-from="G.De_Leeuw@eurofer.be"; helo=smtp.eurofer.be; client-ip=217.64.254.218 Received: from smtp.eurofer.be (217.64.254.218.mactelecom.net [217.64.254.218]) by hosting.mactelecom.net (Postfix) with ESMTP id 9050316611F; Thu, 25 Mar 2010 07:18:52 +0000 (UTC) Received: from [192.168.5.10] by smtp.eurofer.be with esmtpsa (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.69) (envelope-from ) id 1NuhL1-0004Sg-SJ; Thu, 25 Mar 2010 08:18:53 +0100 Message-ID: <4BAB0E53.8020303@eurofer.be> Date: Thu, 25 Mar 2010 08:18:43 +0100 From: Guy Deleeuw User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); fr; rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3 MIME-Version: 1.0 To: Dimitri Fontaine CC: pgsql-admin@postgresql.org References: <4BAA40E2.3000805@eurofer.be> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Spam_score: -4.4 X-SA-Exim-Connect-IP: 192.168.5.10 X-SA-Exim-Mail-From: G.De_Leeuw@eurofer.be Subject: Re: How to use an alias name in the current select X-SA-Exim-Scanned: Yes (on smtp.eurofer.be) X-All-Recipients: dfontaine@hi-media.com, pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201003/187 X-Sequence-Number: 33986 Hello Dimitri, Many thanks for your answers Regards Guy Le 24/03/10 20:09, Dimitri Fontaine a écrit : > Hi, > > Guy Deleeuw 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, >