Re:

The DISTINCT is redundant if your query is part of a
UNION, because UNION by default returns only distinct
rows anyway.  This works for me in PostgreSQL 7.2.1:

SELECT field1, field2 FROM table1
UNION
SELECT field1, NULL FROM table2;

I don't have an Oracle installation handy to test
whether it works there, though.

--- s <smarie@ekno.com> wrote:
> I love the simplicity of this approach.
> Unfortunately it gives the
> same error message.  I guess I'll just have to IFDEF
> my source code.
>
> Thanks,
>
> Sarah
> msarahm@ekno.com
>
> Andrew Sullivan wrote:
>
> On Wed, Jun 26, 2002 at 05:50:44PM +0000, s wrote:
> > 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
>
> Oracle does, IIRC, some strange things with empty
> strings, handling
> '' as NULL, no?  I think this is an Oracle gotcha,
> if I'm right,
> because '' is not NULL, in fact.  (Consider the
> difference between
> knowing that I do not have a middle name, and not
> knowing what my
> middle name is.)
>
> So I wonder if this will work:
>
> select disinct f.id, ' ' from foo f;
>
> The ' ' should get automatically cast to TEXT, and
> you should be all
> right.
>
> A
>
> --
> ----
> Andrew Sullivan                               87
> Mowat Avenue
> Liberty RMS                           Toronto,
> Ontario Canada
> <andrew@libertyrms.info>
>  M6K 3E3
>                                          +1 416 646
> 3304 x110
>
>
>
>
____________________________________________________________________________
> Lonely Planet's ekno - more than a phonecard
> Get ekno before you go!
> http://www.ekno.lonelyplanet.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>


__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com