No title

Started by salmost 24 years ago2 messagesgeneral

smarie@ekno.com

almost 24 years ago

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.

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

Jeff Eckermann

jeff_eckermann@yahoo.com

almost 24 years ago

In reply to: s (#1)

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