Thread: ordering error in query?

I am trying to keep my select statements compatible for running on
oracle or postgres.  I ran into a problem with a query that is part of
a union.  The original query clause is of the form:

select distinct f.id, '' from foo f;

I need the empty string as the second attribute because the first part
of the union has a second attribute.  This works on oracle, but
postgres says:

ERROR:  Unable to identify an ordering operator '<' for type 'unknown'
        Use an explicit ordering operator or modify the query

If I get rid of 'distinct' postgres responds, but I don't get the
result I want.  If I type

select distinct s.ben, cast ('' as varchar) from sitealertsview s;

I get the result I want, but it's not compatible with oracle.

Suggestions?

Thanks,

Sarah
smarie@ekno.com





____________________________________________________________________________
Lonely Planet's ekno - more than a phonecard
Get ekno before you go!
http://www.ekno.lonelyplanet.com

smarie@ekno.com wrote:
> ...
> select distinct f.id, '' from foo f;
>
> I need the empty string as the second attribute because the first part
> of the union has a second attribute.  This works on oracle, but
> postgres says:
>
> ERROR:  Unable to identify an ordering operator '<' for type 'unknown'
>         Use an explicit ordering operator or modify the query
>...
> Suggestions?

   select distinct on (f.id) f.id,'' from foo f;
But "distinct on" is non-standard and Oracle probably won't like it.

Weird-looking, but more likely to work:
   select distinct f.id, ''||'' from foo f;
or even:
   select distinct f.id, tolower('') from foo f;
Either of which seems enough to convince PostgreSQL that we're dealing
with a character type.