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.