pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sort 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