"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:5ihpmqF3p7b65U1@mid.individual.net...
> Now, now, that would be a cheap shot, like making VARCHAR a synonym for
> VARCHAR2. I mean - who would do that??? Oh, never mind, ...
> A measily "2" sure ain't worth a precious bit.
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

If you go down that road, you will have to make following idiotic changes:

columnA = '' (2 single ticks) is the same as columnA is null, and not the
same as columnA = ' ' (tick, blank, tick).

Mark A wrote:
> "Serge Rielau" <srielau@ca.ibm.com> wrote in message
> news:5ihpmqF3p7b65U1@mid.individual.net...
>> Now, now, that would be a cheap shot, like making VARCHAR a synonym for
>> VARCHAR2. I mean - who would do that??? Oh, never mind, ...
>> A measily "2" sure ain't worth a precious bit.
> If you go down that road, you will have to make following idiotic changes:
> columnA = '' (2 single ticks) is the same as columnA is null, and not the
> same as columnA = ' ' (tick, blank, tick).
Hmm, OK, let's - hypothetically - assume DB2 were to make this change,
what behavior would you expect if, say an empty string is LOADed into a
CHAR column? Would that be a NULL or a blank?
Presumably there must also be some functions which treat NULL as empty
string.
E.g. what about TRANSLATE?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:5ii31dF3fi8nfU1@mid.individual.net...
> Hmm, OK, let's - hypothetically - assume DB2 were to make this change,
> what behavior would you expect if, say an empty string is LOADed into a
> CHAR column? Would that be a NULL or a blank?
> Presumably there must also be some functions which treat NULL as empty
> string.
> E.g. what about TRANSLATE?
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

The problem with VARCHAR2 is that if you use '' (tick, tick) for a column
value in a SET statement or a WHERE clause, an SQL exception is thrown if
the column is not nullable. If '' meant null if the column where nullable,
or blank when the column is defined as not null, that might be another
story.

In Oracle, programmers are loath to use CHAR in any situation because ' '
(tick, blank, tick) does not test equal to ' ' (tick, blank, blank, tick).
Of course, as already mentioned, neither of these two would test the same as
'' (tick, tick) which is shorthand for null in Oracle, and will throw an
exception if used on a not null column).

At least the VARCHAR2 gets rid of the trailing blanks, so that string
comparisons will test equal when they are the same. DB2 doesn't care about
trailing blanks, and will test 'ABC' equal to 'ABC ' even in a CHAR column.

So for a LOAD into a CHAR column, it would be OK if an empty string meant
null, but only if the column is nullable. It should mean ' ' (blank) if the
column is not null (and not throw an exception).

Excuse my ignorance, but I am not sure what the reference to TRANSLATE
means.

Mark A wrote:
> Excuse my ignorance, but I am not sure what the reference to TRANSLATE
> means.
In translate a set of characters is replaced for another srt of
characters. In DB2 when the target set in '' (i.e. an empty string) that
means that the found characters are being squeezed out.
In general bot Oracle and DB2 claim that NULL in mans NULL out.
So I wonder whether this is one of those cases where Oracle treats a
NULL as an empty string, hence behaves the same as DB2 (aside from
having the arguments reversed I'm told...)

With these compatibility thingies the devil is in the detail.. :-(

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab