pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSort problem
9+ messages / 5 participants
[nested] [flat]
* Sort problem
@ 2002-02-25 17:17 Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:12 ` Re: [GENERAL] Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:46 ` Re: [SQL] Sort problem Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-25 20:01 ` Re: Sort problem Oliver Elphick <olly@lfix.co.uk>
0 siblings, 3 replies; 9+ messages in thread
From: Samuel J. Sutjiono @ 2002-02-25 17:17 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [GENERAL] Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-25 19:12 ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
2 siblings, 0 replies; 9+ messages in thread
From: Samuel J. Sutjiono @ 2002-02-25 19:12 UTC (permalink / raw)
To: Fernando Schapachnik <fschapachnik@vianetworks.com.ar>; +Cc: pgsql-sql
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.
> >
> > 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-25 19:46 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-25 19:52 ` Re: [SQL] Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 21:53 ` Re: Sort problem Jean-Luc Lachance <jllachan@nsd.ca>
2 siblings, 2 replies; 9+ messages in thread
From: Bruce Momjian @ 2002-02-25 19:46 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:46 ` Re: [SQL] Sort problem Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-02-25 19:52 ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 20:03 ` Re: [SQL] Sort problem Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Samuel J. Sutjiono @ 2002-02-25 19:52 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:46 ` Re: [SQL] Sort problem Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-25 19:52 ` Re: [SQL] Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-25 20:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2002-02-25 20:03 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:46 ` Re: [SQL] Sort problem Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-02-25 21:53 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-02-25 23:09 ` Re: Sort problem Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Jean-Luc Lachance @ 2002-02-25 21:53 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:46 ` Re: [SQL] Sort problem Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-25 21:53 ` Re: Sort problem Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-02-25 23:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 16:41 ` Re: Sort problem Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2002-02-25 23:09 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:46 ` Re: [SQL] Sort problem Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-25 21:53 ` Re: Sort problem Jean-Luc Lachance <jllachan@nsd.ca>
2002-02-25 23:09 ` Re: Sort problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-26 16:41 ` Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Jean-Luc Lachance @ 2002-02-26 16:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sort problem
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-02-25 20:01 ` Oliver Elphick <olly@lfix.co.uk>
2 siblings, 0 replies; 9+ messages in thread
From: Oliver Elphick @ 2002-02-25 20:01 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-02-26 16:41 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-25 17:17 Sort problem Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:12 ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 19:46 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-25 19:52 ` Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-02-25 20:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-25 21:53 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-02-25 23:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 16:41 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-02-25 20:01 ` Oliver Elphick <olly@lfix.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox