Thread: Sort problem
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, My apology. Actually I posted the wrong problem. Here is my problem: IF (SortOrder = ''price'') Then BEGINFor 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" <fschapachnik@vianetworks.com.ar> To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com> 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 >
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
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" <pgman@candle.pha.pa.us>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: <pgsql-general@postgresql.org>; <pgsql-sql@postgresql.org>
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
>
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
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
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
Jean-Luc Lachance <jllachan@nsd.ca> 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
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 <jllachan@nsd.ca> 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