Null value --> 'Unknown'
Posted on 1997-10-10
This question applies to Local SQL using the Borland Database Engine.
Having used Oracle's SQL*Plus for so long, I feel spoiled when I find that I can't do things such as calculations other than substring, sum, count, min, max, etc.
For instance, surely there is a way to translate any field with a null value into some other value such as 'Undefined'.
Oracle's SQL*Plus has the nvl function:
select nvl(Field1, 'Undefined') as NewValue
This would assign the value of Field1 to NewValue unless Field1 has a null value. In that case, it would assign 'Undefined' to NewValue.
Obviously, this can be accomplished without a function using a union statement:
select Field1 as NewValue
where Field1 is not null
select 'Undefined' as NewValue
where Field1 is null
The above query also has a problem in that if 'Undefined' is not the same length as Field1, then the BDE gives an error!