DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=pKNxLy6rdcUhn/dioLK9RWnJspbHSNekvG2BKJpXKYZZ8h+lDpXAZs9m7v7FpdB+T9
	bW6K02+vSWQCyBvUGt8IOnC19eNQ3G3zjWEAKz0S8TDbVr3jSB/sVK1kmiU5XKRlAs+L
	PJ9vdqTzhw3qwrMNOBeR4Oyi8LprpGUFLv5FM=
Message-ID: <4AA92A28.1060902@gmail.com>
Date: Thu, 10 Sep 2009 10:32:40 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: "Little, Douglas" <DOUGLAS.LITTLE@orbitz.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: how to: refer to select list calculations other places
	in the calculations.
References: 
 <A434C531E37AD442815608A769550D80590432A1BC@EGEXCMB01.oww.root.lcl>
	<4AA88783.1040204@gmail.com>
	<A434C531E37AD442815608A769550D80590432A7A3@EGEXCMB01.oww.root.lcl>
In-Reply-To: 
 <A434C531E37AD442815608A769550D80590432A7A3@EGEXCMB01.oww.root.lcl>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I might do the substitution, analyse the query and hope to break it up. 
Can it possibly be optimal?

Little, Douglas wrote:
> Hi Rob, 
> Thanks for the response.
> Repeating the base calculation will work but the calculations are  stunningly complex and long.  Repeating them will undoubtly increase editing errors and maintenance problems. 
>
> Wish I had a sample to post.  Last query I analyzed was approx 10k lines and printed to over 50 pages.   Yes, it was a single query.  I'm amazed that cognos would generate.  Basically they generate a pivoted cross-tab from sql.
>
> Thanks
> Doug
>
>
>
>
> -----Original Message-----
> From: Rob Sargent [mailto:robjsargent@gmail.com] 
> Sent: Wednesday, September 09, 2009 11:59 PM
> To: Little, Douglas
> Subject: Re: [SQL] how to: refer to select list calculations other places in the calculations.
>
> Would reverting to the base columns work?
>
> select a, b, a+b as c, (a+b)*d, (a+b)*f etc.
>
> Little, Douglas wrote:
>   
>> Hi,
>>
>> I have very complex sql (generated by cognos) I'm porting from 
>> Teradata that basically uses column alias in the calculated columns.
>>
>> Is there any way to do this type of thing in Psql?
>>
>> Essentially I need to do is refer to a calculated column later in the 
>> select list (but far more complex - like 50 pages printed out)
>>
>> sel a,b,a+b as c, c*d, c*f, c*g, c*h, c*I, c*j, c*k,...... --- where c is 
>> a calculated column used in other calculations.
>>
>> from ....
>>
>> **Doug Little**
>>
>> Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz 
>> Worldwide
>>
>> 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 
>> 312.894.5164 | Cell 847-997-5741
>>
>>
>>       ***Douglas.Little@orbitz.com* <mailto:Douglas.Little@orbitz.com>**__
>>
>> orbitz.com <http://www.orbitz.com/> | ebookers.com 
>> <http://www.ebookers.com/> | hotelclub.com <http://www.hotelclub.com/> 
>> | cheaptickets.com <http://www.cheaptickets.com/> | ratestogo.com 
>> <http://www.ratestogo.com/> | asiahotels.com <http://www.asiahotels.com/>
>>
>>