From DOUGLAS.LITTLE@orbitz.com Wed Sep 9 13:33:29 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D9A22633AF0 for ; Wed, 9 Sep 2009 10:33:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 62609-09 for ; Wed, 9 Sep 2009 13:33:05 +0000 (UTC) X-Greylist: delayed 00:05:34.410432 by SQLgrey-1.7.6 Received: from exprod7og101.obsmtp.com (exprod7og101.obsmtp.com [64.18.2.155]) by mail.postgresql.org (Postfix) with SMTP id 168C9633557 for ; Wed, 9 Sep 2009 10:33:12 -0300 (ADT) Received: from source ([198.151.60.5]) by exprod7ob101.postini.com ([64.18.6.12]) with SMTP ID DSNKSqeul5vQ47U9Kye/AH9G4Foy1jtWwe7V@postini.com; Wed, 09 Sep 2009 06:33:12 PDT Received: from EGEXCMB01.oww.root.lcl ([192.168.8.23]) by egexccaht02.oww.root.lcl ([10.235.43.215]) with mapi; Wed, 9 Sep 2009 08:27:36 -0500 From: "Little, Douglas" To: "pgsql-sql@postgresql.org" Date: Wed, 9 Sep 2009 08:27:35 -0500 Subject: how to: refer to select list calculations other places in the calculations. Thread-Topic: how to: refer to select list calculations other places in the calculations. Thread-Index: AcoxUUrKRJqFKhyXRO2SuePmwBScOg== Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: yes X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/related; boundary="_004_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_"; type="multipart/alternative" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.816 tagged_above=-10 required=5 tests=BAYES_40=-0.185, EXTRA_MPART_TYPE=1, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200909/37 X-Sequence-Number: 33102 --_004_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_ Content-Type: multipart/alternative; boundary="_000_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_" --_000_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I have very complex sql (generated by cognos) I'm porting from Teradata tha= t 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 selec= t 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 Worldwid= e 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312= .894.5164 | Cell 847-997-5741 Douglas.Little@orbitz.com [cid:image002.jpg@01CA3127.61E69F30] orbitz.com = | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com --_000_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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?<= /o:p>

Essentially I need to do is refer to a calculated column later in the select list (but far more complex – like 50 pages printe= d out)

sel a,b,a+b as c, c*d, c*f, c*g, c*h, c*I, c*j, c*k,R= 30;… --- where c is a calculated column used in ot= her calculations.

from ....

Doug Little=

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Wor= ldwide

500 W. M= adison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com<= /font>
orbitz.com<= /span> | ebookers.com | hotelclub.com = | cheaptickets.com | ratestogo.com<= font color=3Dblue> | asiahotels.com

--_000_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_-- --_004_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_ Content-Type: image/jpeg; name="image002.jpg" Content-Description: image002.jpg Content-Disposition: inline; filename="image002.jpg"; size=2337; creation-date="Wed, 09 Sep 2009 08:27:35 GMT"; modification-date="Wed, 09 Sep 2009 08:27:35 GMT" Content-ID: Content-Transfer-Encoding: base64 /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwgHBgoICAgLCgoLDhgQDg0NDh0VFhEYIx8lJCIf IiEmKzcvJik0KSEiMEExNDk7Pj4+JS5ESUM8SDc9Pjv/2wBDAQoLCw4NDhwQEBw7KCIoOzs7Ozs7 Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozs7Ozv/wAARCAA9AFMDASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwDnNPsL OSwt2a1hLGJSSYxzxWxL4TWNmVbGCQqQDtjA5PQDPX8Kr6DYXV9p8H2aFpdkKbsEcZFdRDZa0X3v bEMWV2YKoJYdCasx1OePhM+aY006CQ5AyiKQSQD/AFFNPhbHP9mwY27iQqYAxnOfoDXWraaqCCFY EHd0HBwBn9B+VD2GotH5Yh2p5YjIUD5gBjn8zQI5OTwp5cmw6dATjOQi9PWki8Mwyg/6JbI3meUE aMAl8dOldd9k1UhhtOG+9wPm+tRppuoxghYyNzb84GQ3qPSgLnLf8IqPs32g6fAqFlUZRcsT6Usf hPzTMq6fAHhVWKFBlgTxj19a6z7LquwJtOA28cL971+tMOn6mWJCspKqvy4HA4HSgdzmn8HGOeWI 2Nr+6BJfC7SACcj2ODis5tKs1HNnB/37FdzLBq3lFVt9zMMM5AJK8jH05NYk+g6qckWT4+o/xoB3 OE1C2t0vZFW3iAGOAg9BRUupf8f0n0X+QoqS1sd/8Nk32LD/AKYxfyNV9S8c6tPq0troVsjRQlgP 3XmM4HVvYVH4PuZbPwnqE9uAZltIhGCe5BH51F4Qi/s/Tr67lylxdIsUI5DBc5Yn2PFduHhHlc2r mM520TNHSfGGsfY9SutVhi2W9sJIiI9vzscIPfP9KzD4z8Wf2d/aBjgFoX8sTeQNpb060eLZTBZW OlxbjLckTyqPyjH8zWbPBqtvqVj4c1HEscEyYtEcbRuPIyO+DXXGnB62X/ARHPK2rOp1vxhqNhou kPEkKahexebKCmQF6DA96rWXibxWPEdnpWoW0KSTOu6FoQrbT34PHGTWRfTWfiTxrMZ79NPsY/ki mPREQYUD6mm6XqD6bf6vqUNwbtYYWiiuZhl2ZjtQgnp3P0FT7KCjbl1/z2K53fc6FfF2qXviq5st Oijlsrcu21YtzuqDnB9zwPrWQnjbxXLeG0jhia4BIMK22WBHUY9q0fBCLoHhbU/Esw/eMpSHPfH+ LEflS/DWxMkt9r102NuUEjHufmds1LVOCk+W9tPmNOTtruN1PxL4s03TreSa0CSMu+eVrbCJn7qf XufrVjwtrfiLxBO8lw8CWMA/eMIQC5x90H+dYfinxKviXV4rQXX2bS4XwHIJ3erkDr7Cu50S70eX SBbaGzNa2w2ljGVy3c5PU+tZ1IqFLWOrHF3lvoePaj/x+v8ARf8A0EUUaj/x+v8ARf8A0EUV5+pq tjvPh5bQXelvDOpZDFCeDgg4NdX9g0K3nw0crshPykkjgD/ECuW+HDhLEkkD9zF1Psa6+K0hS5W4 efeQ7OVOMEnp+VbQlZNXIlG9nYr3Og6FP4jj1GeC4kvfMQglzsRgPlBH0HSo18O6BP4gmvPLumvJ GkJkMh2g4w2PTGeK0Yo1S5aZp9/zs6DA+Vm4yfXA4pkNtFbpL5c+Hlj2F8d+ct9STVc7XUVn2MP/ AIQPwp9kNz5F35Q6fvjk844qxJ4W8MPpEdqYLiG3WZiQHIYuAclj3wM1rPGWtYYBcovksCCEGDjo MZpsttDLDDEJiqxbj2O5j3P480/at7yDl7IrXGk6JfeH4dNdLgWNuU2IjFSWPQE9zzSto+jw6YPD iJcpaswDeW5G4tzhm96txxwxRW0QlykDbjn+NsdT/OiKJUuGme43kuzqNoG1iMZPrgcVPN5js+xg DwJ4WkkkSO2vDsJBczHbkda2I4Leysks7SMRQxLhVH+etPhRLSApvR2J5cLgn6+pqNmUqx3Doe9K pUlLRu5UIpK54rqP/H8/0X+Qoo1H/j+f6L/IUViC2NfS/wDkHWv/AFxT+VblkFdJXaXYsSb2OM8Z A/rXJWWreVY26eRnbEozv9vpVqLxDLAWMUZXcMH5wcjOe49QKolxOzW0uyXyUVEBJctxgAnP44qN UuC0AYhRO21DnPOM4/UfnXKjxXehSA0mD1zIDnr7e5/Omv4nuZAgYOfLbcvzjg8c9PYflRcOU7N7 K9QLyhLYwM9z1H1B4PuaRLO+a4WFgqkuEY7s7TwT+QINccPFd6DkNJ94tzJn5iQSeR1yAfwpf+Es vfMMm6QuzbixkBOfXp7Ci4cp1iQXsjII1VvMz5eHHz464+nekiiupVjcABZASp3ZyB7Vya+KbtEV EMiqv3QJPu9+OOKcPF18OjOPl28OBx6fd6UXDlOqa1u3O1lT7/l58wY3f3c+tYs7Z5BqjD4wvICz KhYsxb53zhj1Ycdap/23x/qD/wB9/wD1qA5SpqX/AB/yfh/IUVVvb0S3bv5WM443ew9qKktbH//Z --_004_A434C531E37AD442815608A769550D80590432A1BCEGEXCMB01owwr_-- From robjsargent@gmail.com Thu Sep 10 16:32:54 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E46A7634A15 for ; Thu, 10 Sep 2009 13:32:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 62352-09 for ; Thu, 10 Sep 2009 16:32:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f191.google.com (mail-pz0-f191.google.com [209.85.222.191]) by mail.postgresql.org (Postfix) with ESMTP id DA05163412E for ; Thu, 10 Sep 2009 13:32:45 -0300 (ADT) Received: by pzk29 with SMTP id 29so213423pzk.14 for ; Thu, 10 Sep 2009 09:32:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=7GLjXdgXsyujiDt1Sm+aRlpihRBwYLOc7gOQd0EkK9k=; b=OUADD7RIbOyQYXcSdUprmhyVg/+g+e8aWklfLAGlRCiwb+US2rqD9udAUWcEEigDrf kcjvQhiaYW28oIiNxoCmc07s9AHkgS1CdXTxlatWleWOh9r77WuytZeYEDBYzEshnrK/ TVcf7ihmIhqIewRvf8LKp4BuAlcyiAein4DrU= 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= Received: by 10.114.253.13 with SMTP id a13mr3251987wai.13.1252600363737; Thu, 10 Sep 2009 09:32:43 -0700 (PDT) Received: from ?192.168.1.77? ([66.183.181.157]) by mx.google.com with ESMTPS id 20sm1847925pzk.13.2009.09.10.09.32.41 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 10 Sep 2009 09:32:43 -0700 (PDT) Message-ID: <4AA92A28.1060902@gmail.com> Date: Thu, 10 Sep 2009 10:32:40 -0600 From: Rob Sargent User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: "Little, Douglas" , "pgsql-sql@postgresql.org" Subject: Re: how to: refer to select list calculations other places in the calculations. References: <4AA88783.1040204@gmail.com> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.759 tagged_above=-10 required=5 tests=BAYES_20=-0.74, URIBL_SBL=1.499 X-Spam-Level: X-Archive-Number: 200909/43 X-Sequence-Number: 33108 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* **__ >> >> orbitz.com | ebookers.com >> | hotelclub.com >> | cheaptickets.com | ratestogo.com >> | asiahotels.com >> >> From Bernd.Nawothnig@t-online.de Sat Oct 31 21:11:42 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 497E96367B8 for ; Sat, 31 Oct 2009 18:11:42 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 44037-10 for ; Sat, 31 Oct 2009 21:11:26 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mailout01.t-online.de (mailout01.t-online.de [194.25.134.80]) by mail.postgresql.org (Postfix) with ESMTP id 9F95F63566E for ; Sat, 31 Oct 2009 18:11:31 -0300 (ADT) Received: from fwd06.aul.t-online.de by mailout01.t-online.de with smtp id 1N4LEN-0000Ub-01; Sat, 31 Oct 2009 22:11:27 +0100 Received: from miraculix.hinkelstein (XV8jTUZ6QhJGyetLegNA3frWlIQBGqt00-it-IfftYE-UQ7IteGbAxVYv-K+LQVgBy@[93.204.42.163]) by fwd06.aul.t-online.de with esmtp id 1N4LED-0JfGr20; Sat, 31 Oct 2009 22:11:17 +0100 Received: by miraculix.hinkelstein (Postfix, from userid 1000) id 41D6234745E6; Sat, 31 Oct 2009 22:11:06 +0100 (CET) Date: Sat, 31 Oct 2009 22:11:06 +0100 From: Bernd.Nawothnig@t-online.de (Bernd Nawothnig) To: "Little, Douglas" Cc: "pgsql-sql@postgresql.org" Subject: Re: how to: refer to select list calculations other places in the calculations. Message-ID: <20091031211106.GA9238@miraculix> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-ID: XV8jTUZ6QhJGyetLegNA3frWlIQBGqt00-it-IfftYE-UQ7IteGbAxVYv-K+LQVgBy X-TOI-MSGID: 4bfa57a7-743c-4b1c-9a39-f308dea908fb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.11 tagged_above=-10 required=5 tests=BAYES_05=-1.11 X-Spam-Level: X-Archive-Number: 200911/30 X-Sequence-Number: 33381 On Wed, Sep 09, 2009 at 08:27:35AM -0500, Little, Douglas wrote: > 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? I use nested subqueries and do the renaming of calculated columns in the innermost queries in such cases. Bernd From the6campbells@gmail.com Fri Nov 6 19:45:28 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D4524633468 for ; Fri, 6 Nov 2009 15:45:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 54054-02 for ; Fri, 6 Nov 2009 19:45:15 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com [209.85.218.213]) by mail.postgresql.org (Postfix) with ESMTP id A0187632DAF for ; Fri, 6 Nov 2009 15:45:17 -0400 (AST) Received: by bwz5 with SMTP id 5so1577791bwz.3 for ; Fri, 06 Nov 2009 11:45:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=6BWIdeLqDUu2Lhu5wenAXE4C5tPZ5f7TOShOZVCQulY=; b=sX17bjTzOL5y6uf4WckeRDg5GlMX7OJG412GcnEuh11VTn+8oiEoxyB2DqeAa/yRas l8JAopdcMwai1Yl+VemPow7JNEvZTO8pQrxeIZAUm62KrdAHrOOkLsAxDdlVBozZDFl5 Jy2oE1zFce/h38IuvH7egMjYYJrKpBYOjoVLU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=yIMqU3y42uztsfVKE5KcXLSFc7ESE7NzLN0mZ0Fx8a8hKBLU/bl21T2NwxxDU7oar5 njckbVWZ2Ve4fks3YS8lXI0MFeF2yItyWV/UnPFsqFSx92GI1eXENVWZl0ypu8nynyY2 KigNokFqQkUCUmMP5xZvs/0/9tZBYmTMrtEYU= MIME-Version: 1.0 Received: by 10.204.15.3 with SMTP id i3mr5054717bka.71.1257536715385; Fri, 06 Nov 2009 11:45:15 -0800 (PST) In-Reply-To: <20091031211106.GA9238@miraculix> References: <20091031211106.GA9238@miraculix> Date: Fri, 6 Nov 2009 14:45:15 -0500 Message-ID: <22431f1b0911061145o39013cfem8d4e7a390f9a9ea@mail.gmail.com> Subject: Re: how to: refer to select list calculations other places in the calculations. From: the6campbells To: Bernd Nawothnig Cc: "Little, Douglas" , "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=00032555a0be366d5f0477b91523 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.114 tagged_above=-10 required=5 tests=AWL=0.484, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/33 X-Sequence-Number: 33384 --00032555a0be366d5f0477b91523 Content-Type: text/plain; charset=ISO-8859-1 SQL (unlike MDX) does not allow you to define an expressions and reference it elsewhere in the same block by name. As suggested, a hand written or auto-generated statement can try to refactor the statement by pushing things into a derived table and the outer blocks refer to the projected item names. Not only can this make the SQL more readable it can assist some vendors who internally may not do a great job of common expression factoring. This is one reason why common table expressions are used. For example, if you are in report studio, you can copy the data items into another Bi-Query which you reference from the query that consumes it. On Sat, Oct 31, 2009 at 4:11 PM, Bernd Nawothnig < Bernd.Nawothnig@t-online.de> wrote: > On Wed, Sep 09, 2009 at 08:27:35AM -0500, Little, Douglas wrote: > > > 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? > > I use nested subqueries and do the renaming of calculated columns in the > innermost queries in such cases. > > > > > Bernd > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > --00032555a0be366d5f0477b91523 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

SQL (unlike MDX) does not allow you to define an expressions and refer= ence it elsewhere in the same block by name. As suggested, a hand written o= r auto-generated statement can try to refactor the statement by pushing thi= ngs into a derived table and the outer blocks refer to the projected item n= ames. Not only can this make the SQL more readable it can assist some vendo= rs who internally may not do a great job of common expression factoring. Th= is is one reason why common table expressions are used.

For example, if you are in report studio, you can copy the data items = into another Bi-Query which you reference from the query that consumes it. =

On Sat, Oct 31, 2009 at 4:11 PM, Bernd Nawothnig= <Bernd= .Nawothnig@t-online.de> wrote:

On Wed, Sep 09, 2009 at 08:27:35= AM -0500, Little, Douglas wrote:

> I have very complex sql (gener= ated 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?

I use nest= ed subqueries and do the renaming of calculated columns in the
innermost= queries in such cases.

Bernd

--
Sent via pgsql-s= ql mailing list (pgsql-sql@post= gresql.org)
To make changes to your subscription:
http://www.post= gresql.org/mailpref/pgsql-sql

--00032555a0be366d5f0477b91523--