unexpected result from COALESCE

[John Greve]

Any ideas on why my the result 'green' row
column DELTA does not yield -1 for?
That is not how I expected COALESCE( ... ) to work.

Every other row for DELTA (including 'black')
comes out the way I expected.

Thanks for considering this,
John G.

note: running as400, on V5-R2
create table blarch (
a char(10),
b char(10)
);
insert into blarch values( 'red', '123' );
insert into blarch values( 'blue', '0' );
insert into blarch values( 'green', '' );
insert into blarch values( 'black', null );
select
'>>' || a || '<<' as ALPHA ,
'>>' || b || '<<' as BETA ,
cast( b as INTEGER) as GAMMA,
coalesce( cast( b as INTEGER), -1 ) as DELTA
from blarch;

test
| ALPHA | BETA | GAMMA | DELTA |
+--------------+--------------+-----------+-----------+
|>>red <<|>>123 <<| 123| 123|
|>>blue <<|>>0 <<| 0| 0|
|>>green <<|>> <<| -| -|
|>>black <<|- | -| -1|
+--------------+--------------+-----------+-----------+

[Serge Rielau]

John Greve wrote:

test
| ALPHA | BETA | GAMMA | DELTA |
+--------------+--------------+-----------+-----------+
|>>red <<|>>123 <<| 123| 123|
|>>blue <<|>>0 <<| 0| 0|
|>>green <<|>> <<| -| -|
|>>black <<|- | -| -1|
+--------------+--------------+-----------+-----------+

Here is what I get on DB2 for LUW.
Do you have soem form of friendly-arithmetic goin on that
recovers from the error and fails the whole expression (including the
COALESCE) to NULL maybe?

ALPHA BETA GAMMA DELTA
-------------- -------------- ----------- -----------

red << >>123 << 123 123
blue << >>0 << 0 0

SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

[John Greve]

Thanks, Serge - yes, it does seem to be "friendly" arithmetic at first.
Casting an all-space CHAR value to INTEGER generates a NULL
on DB/2-400. What I am stuck on is why doesn't COALESCE notice
the NULL and, uh... coalesce? Column GAMMA yields NULL for
rows "green" and "black". Why then isn't column DELTA
the same in those rows? You'll recall that DELTA is defined
in the query as:
coalesce( cast( b as INTEGER), -1) as DELTA
I expected -1 to be next in line for consideration by COALESCE
once it found a null resulting from the CAST.

It seems that NULL values are typed in this corner
of the DB/2 multiverse. That is something I have not
seen before in relational-land. Is there any possible
benefit to having different "flavors" of NULL ?

Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<nX*******************@twister01.bloor.is.net .cable.rogers.com>...

John Greve wrote:

test
| ALPHA | BETA | GAMMA | DELTA |
+--------------+--------------+-----------+-----------+
|>>red <<|>>123 <<| 123| 123|
|>>blue <<|>>0 <<| 0| 0|
|>>green <<|>> <<| -| -|
|>>black <<|- | -| -1|
+--------------+--------------+-----------+-----------+

Here is what I get on DB2 for LUW.
Do you have soem form of friendly-arithmetic goin on that
recovers from the error and fails the whole expression (including the
COALESCE) to NULL maybe?

ALPHA BETA GAMMA DELTA
-------------- -------------- ----------- -----------

>>red << >>123 << 123 123
>>blue << >>0 << 0 0

SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

[AK]

can you first COALESCE, then CAST?

[Serge Rielau]

John I can only gues that DB2 for iSeries not only NULLs the failing
function, but the whole expression. So the COALESCE (allegedly) never
executes. Pure guesswork on my part....

Cheers
Serge

[John Greve]

ak************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>...

can you first COALESCE, then CAST?

Start by using COALESCE on the value raw value from disk?
Hmm... for the problem row, the column
in question is actually not null - it
is space filled.

COALESCE would see that, and say "Not null, ok - keep it."
Then CAST would say "Hmm... space, I'll convert that to null."

Since first posting this, I have come up with a "workaround".
cast( '0' || b as INTEGER)

To be null proof, it would be best to use something like this:
cast( '0' || COALESCE(b, '') as INTEGER )

It seems that prefixing the characters with an extra zero
is fairly harmless; fortunately negative numbers aren't
an issue in this application. I doubt cast() would be
very happy with '0-1234'.

Why the database creator put numbers in a CHAR column I'll never know.
Oh well, thanks Serge and AK for pondering this.