Sort problem

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" <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.

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.

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

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

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: