From ssutjiono@wc-group.com Mon Feb 25 17:21:37 2002 Received: from qmsql.netservers.net (hpmail3.netservers.net [209.196.128.174]) by postgresql.org (Postfix) with SMTP id 2994947685F for ; Mon, 25 Feb 2002 12:21:37 -0500 (EST) Received: (qmail 96694 invoked from network); 25 Feb 2002 17:21:37 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail3.netservers.net with SMTP; 25 Feb 2002 17:21:37 -0000 Message-ID: <016001c1be20$3d65fed0$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: , Subject: Sort problem Date: Mon, 25 Feb 2002 12:17:01 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_015D_01C1BDF6.53EF9820" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200202/1218 X-Sequence-Number: 21210 This is a multi-part message in MIME format. ------=_NextPart_000_015D_01C1BDF6.53EF9820 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I was trying to do the following sort (two attributes with different types)= , but I kept on getting errors.=20 ORDER BY Price (data type - decimal), ProductName (data type - varchar) Thanks for any help. ------=_NextPart_000_015D_01C1BDF6.53EF9820 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I was trying to do the following sort (two= =20 attributes with different types), but I kept on getting=20 errors. 
 
ORDER BY Price (data type - decimal), Prod= uctName=20 (data type - varchar)
 
Thanks for any help.
 
 
------=_NextPart_000_015D_01C1BDF6.53EF9820-- From ssutjiono@wc-group.com Mon Feb 25 19:17:02 2002 Received: from qmsql.netservers.net (hpmail3.netservers.net [209.196.128.174]) by postgresql.org (Postfix) with SMTP id 4C4AB475E2D for ; Mon, 25 Feb 2002 14:17:02 -0500 (EST) Received: (qmail 62485 invoked from network); 25 Feb 2002 19:17:02 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail3.netservers.net with SMTP; 25 Feb 2002 19:17:02 -0000 Message-ID: <01d001c1be30$5d0a5370$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: "Fernando Schapachnik" Cc: References: <016001c1be20$3d65fed0$110a010a@headquarters.wcgroup.com> <20020225155918.C65115@ns1.via-net-works.net.ar> Subject: Re: [GENERAL] Sort problem Date: Mon, 25 Feb 2002 14:12:26 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200202/364 X-Sequence-Number: 6786 Fernando, My apology. Actually I posted the wrong problem. Here is my problem: IF (SortOrder = ''price'') Then BEGIN For rec_set IN SELECT * from Catalog where ((VendorName ~* SearchString) ORDER BY CASE WHEN SortOrder = ''store'' Then VendorName WHEN SortOrder = ''category'' Then ProductCategory Else Price END Here is the error message: ERROR: CASE types "numeric" and "varchar" not matched. By the way, do you know how to do a variable (that contains 'attribute name' instead of 'attribute value') substitution. For instance, if the attribute name 'Price' is passed to the sort parameter ($1) so I can just do ORDER BY $1 instead of doing case statement. Thanks so much for your help. ----- Original Message ----- From: "Fernando Schapachnik" To: "Samuel J. Sutjiono" Sent: Monday, February 25, 2002 1:59 PM Subject: Re: [GENERAL] Sort problem > Maybe someone can help you if you post your query and the error > message. Is like going to the doctor: you don't has claim: "it > hurts". > > Good luck > > En un mensaje anterior, Samuel J. Sutjiono escribió: > > I was trying to do the following sort (two attributes with different types), but I kept on getting errors. > > > > ORDER BY Price (data type - decimal), ProductName (data type - varchar) > > > > Thanks for any help. > > > > > > > > Fernando P. Schapachnik > Gerente de tecnología de red > y sistemas de información > VIA NET.WORKS ARGENTINA S.A. > fschapachnik@vianetworks.com.ar > Tel.: (54-11) 4323-3381 > From pgman@candle.pha.pa.us Mon Feb 25 19:46:43 2002 Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (Postfix) with ESMTP id 03827475FFE; Mon, 25 Feb 2002 14:46:43 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g1PJkbn04775; Mon, 25 Feb 2002 14:46:37 -0500 (EST) From: Bruce Momjian Message-Id: <200202251946.g1PJkbn04775@candle.pha.pa.us> Subject: Re: [SQL] Sort problem In-Reply-To: <016001c1be20$3d65fed0$110a010a@headquarters.wcgroup.com> To: "Samuel J. Sutjiono" Date: Mon, 25 Feb 2002 14:46:37 -0500 (EST) Cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL96 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200202/1223 X-Sequence-Number: 21215 Samuel J. Sutjiono wrote: > I was trying to do the following sort (two attributes with different types), but I kept on getting errors. > > ORDER BY Price (data type - decimal), ProductName (data type - varchar) Would you share the errors with us? :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 From ssutjiono@wc-group.com Mon Feb 25 19:57:36 2002 Received: from qmsql.netservers.net (hpmail3.netservers.net [209.196.128.174]) by postgresql.org (Postfix) with SMTP id B47CF476469 for ; Mon, 25 Feb 2002 14:57:36 -0500 (EST) Received: (qmail 24323 invoked from network); 25 Feb 2002 19:57:32 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail3.netservers.net with SMTP; 25 Feb 2002 19:57:31 -0000 Message-ID: <024901c1be36$04f87df0$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: "Bruce Momjian" Cc: , References: <200202251946.g1PJkbn04775@candle.pha.pa.us> Subject: Re: [SQL] Sort problem Date: Mon, 25 Feb 2002 14:52:55 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200202/1224 X-Sequence-Number: 21216 Bruce, My apology. Actually I posted the wrong problem. Here is my problem: IF (SortOrder = ''price'') Then BEGIN For rec_set IN SELECT * from Catalog where ((VendorName ~* SearchString) ORDER BY CASE WHEN SortOrder = ''store'' Then VendorName WHEN SortOrder = ''category'' Then ProductCategory Else Price END Here is the error message: ERROR: CASE types "numeric" and "varchar" not matched. By the way, do you know how to do a variable (that contains 'attribute name' instead of 'attribute value') substitution. For instance, if the attribute name 'Price' is passed to the sort parameter ($1) so I can just do ORDER BY $1 instead of doing case statement. Thanks so much for your help. Sam ----- Original Message ----- From: "Bruce Momjian" To: "Samuel J. Sutjiono" Cc: ; Sent: Monday, February 25, 2002 2:46 PM Subject: Re: [SQL] Sort problem > Samuel J. Sutjiono wrote: > > I was trying to do the following sort (two attributes with different types), but I kept on getting errors. > > > > ORDER BY Price (data type - decimal), ProductName (data type - varchar) > > Would you share the errors with us? :-) > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > From olly@lfix.co.uk Mon Feb 25 21:33:49 2002 Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by postgresql.org (Postfix) with ESMTP id 7CDC4475BCB; Mon, 25 Feb 2002 16:33:49 -0500 (EST) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-34.mail.demon.net with esmtp (Exim 3.34 #1) id 16fSko-000Etv-0Y; Mon, 25 Feb 2002 21:33:50 +0000 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.34 #1 (Debian)) id 16fRJ8-0000Bs-00; Mon, 25 Feb 2002 20:01:10 +0000 Subject: Re: Sort problem From: Oliver Elphick To: "Samuel J. Sutjiono" Cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org In-Reply-To: <016001c1be20$3d65fed0$110a010a@headquarters.wcgroup.com> References: <016001c1be20$3d65fed0$110a010a@headquarters.wcgroup.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 25 Feb 2002 20:01:10 +0000 Message-Id: <1014667270.5761.15.camel@linda> Mime-Version: 1.0 X-Archive-Number: 200202/1236 X-Sequence-Number: 21228 On Mon, 2002-02-25 at 17:17, Samuel J. Sutjiono wrote: > I was trying to do the following sort (two attributes with different types), but I kept on getting errors. > > ORDER BY Price (data type - decimal), ProductName (data type - varchar) There doesn't seem to be anything wrong with that phrase. You need to show us the whole query and the error messages. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Peace I leave with you, my peace I give unto you; not as the world giveth, give I unto you. Let not your heart be troubled, neither let it be afraid." John 14:27 From pgman@candle.pha.pa.us Mon Feb 25 20:03:46 2002 Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (Postfix) with ESMTP id 9F2B7476647; Mon, 25 Feb 2002 15:03:46 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g1PK3gO07141; Mon, 25 Feb 2002 15:03:42 -0500 (EST) From: Bruce Momjian Message-Id: <200202252003.g1PK3gO07141@candle.pha.pa.us> Subject: Re: [SQL] Sort problem In-Reply-To: <024901c1be36$04f87df0$110a010a@headquarters.wcgroup.com> To: "Samuel J. Sutjiono" Date: Mon, 25 Feb 2002 15:03:42 -0500 (EST) Cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL96 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200202/1226 X-Sequence-Number: 21218 Samuel J. Sutjiono wrote: > Bruce, > > My apology. Actually I posted the wrong problem. Here is my problem: > > IF (SortOrder = ''price'') Then > BEGIN > For rec_set IN > SELECT * from Catalog where ((VendorName ~* SearchString) > ORDER BY CASE WHEN SortOrder = ''store'' Then VendorName > WHEN SortOrder = ''category'' Then > ProductCategory Else Price END > > Here is the error message: > ERROR: CASE types "numeric" and "varchar" not matched. > > By the way, do you know how to do a variable (that contains 'attribute name' > instead of 'attribute value') substitution. For instance, if the attribute > name 'Price' is passed to the sort parameter ($1) so I can just do ORDER BY > $1 instead of doing case statement. The CASE can't return on type of data in one branch and a different type in another. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 From jllachan@nsd.ca Mon Feb 25 21:43:21 2002 Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 151FA475D23 for ; Mon, 25 Feb 2002 16:43:21 -0500 (EST) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id QAA06889 for ; Mon, 25 Feb 2002 16:43:22 -0500 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma006887; Mon, 25 Feb 02 16:43:01 -0500 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id QAA32696 for ; Mon, 25 Feb 2002 16:44:53 -0500 Message-ID: <3C7AB259.A66B2282@nsd.ca> Date: Mon, 25 Feb 2002 16:53:29 -0500 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-12 i686) X-Accept-Language: en MIME-Version: 1.0 Cc: pgsql-sql@postgresql.org Subject: Re: Sort problem References: <200202251946.g1PJkbn04775@candle.pha.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/370 X-Sequence-Number: 6792 Hi all, Can someone (Tom maybe) please tell me why one can not use an alias from the select list in the where clause or the order by clause. It would make things much easier where using computed fields. JLL From tgl@sss.pgh.pa.us Mon Feb 25 23:09:55 2002 Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id BA5A24761F9 for ; Mon, 25 Feb 2002 18:09:55 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1PN9LS25160; Mon, 25 Feb 2002 18:09:21 -0500 (EST) To: Jean-Luc Lachance Cc: pgsql-sql@postgresql.org Subject: Re: Sort problem In-reply-to: <3C7AB259.A66B2282@nsd.ca> References: <200202251946.g1PJkbn04775@candle.pha.pa.us> <3C7AB259.A66B2282@nsd.ca> Comments: In-reply-to Jean-Luc Lachance message dated "Mon, 25 Feb 2002 16:53:29 -0500" Date: Mon, 25 Feb 2002 18:09:21 -0500 Message-ID: <25157.1014678561@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/372 X-Sequence-Number: 6794 Jean-Luc Lachance writes: > Can someone (Tom maybe) please tell me why one can not use an alias from > the select list in the where clause or the order by clause. Eh? You can do it in the ORDER BY clause: regression=# select f1 as alias from int4_tbl order by alias; alias ------------- -2147483647 -2147483647 -123456 -123456 0 0 123456 123456 2147483647 2147483647 (10 rows) As for WHERE, the reason you can't do it there is that it wouldn't be meaningful. The SELECT list is not calculated until *after* the WHERE conditions are applied. Any SQL book should tell you about the stages of processing of a SELECT query... regards, tom lane From jllachan@nsd.ca Tue Feb 26 16:31:10 2002 Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id DD8554767FE for ; Tue, 26 Feb 2002 11:31:10 -0500 (EST) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id LAA10770; Tue, 26 Feb 2002 11:31:15 -0500 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma010768; Tue, 26 Feb 02 11:30:57 -0500 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id LAA06392; Tue, 26 Feb 2002 11:32:52 -0500 Message-ID: <3C7BBABC.AFB24FF4@nsd.ca> Date: Tue, 26 Feb 2002 11:41:32 -0500 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-12 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: Sort problem References: <200202251946.g1PJkbn04775@candle.pha.pa.us> <3C7AB259.A66B2282@nsd.ca> <25157.1014678561@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/399 X-Sequence-Number: 6821 Tom, You are right about the ORDER BY CLAUSE, but the parser should be able to rewrite the query so that I do not have to retype the whole thing... Tom Lane wrote: > > Jean-Luc Lachance writes: > > Can someone (Tom maybe) please tell me why one can not use an alias from > > the select list in the where clause or the order by clause. > > Eh? You can do it in the ORDER BY clause: > > regression=# select f1 as alias from int4_tbl order by alias; > alias > ------------- > -2147483647 > -2147483647 > -123456 > -123456 > 0 > 0 > 123456 > 123456 > 2147483647 > 2147483647 > (10 rows) > > As for WHERE, the reason you can't do it there is that it wouldn't be > meaningful. The SELECT list is not calculated until *after* the WHERE > conditions are applied. Any SQL book should tell you about the stages > of processing of a SELECT query... > > regards, tom lane