I am working with SQL Functions in DB2 for Windows/Linux/UNIX (V8.2.1) and
am having a problem setting input parameters for SQL Functions to null in
the Development Center.
My simple function, called Half, has a single integer input parameter and
divides this value by two to produce its result. It also tests the input
parameter to see if it is null; if it is null, it returns null. When I click
the Run button and try to use the SET TO NULL button to set the input
parameter to null, the button is greyed out, making it impossible to set the
value to null.
I looked in the Information Center and found an article entitled 'Specifying
run settings' and it says something very interesting:
- Set selected STRING [emphasis added] parameters to null by clicking Set to
Null.
This suggests that the Development Center is "working as designed" by not
letting me set an integer (or date or other non-numeric) to null!
So what is the correct way to set null values in non-numeric fields? And why
are numerics and non-numerics handled differently by the Development Center?
It seems to me that the datatype of the input parameter shouldn't make any
difference as far as setting nulls goes: it should be possible to set any
datatype to null by some method. From a software design point of view, I
don't see why there should be a different approach for setting numerics to
nulls than for setting non-numerics to nulls.
By the way, I know about "RETURNS NULL ON NULL INPUT" and its opposite,
"CALLED ON NULL INPUT" and I know that I could use the former to avoid
having to handle nulls in the input data and avoid this problem in the first
place. But I'd like to know how to supply nulls during testing if I choose
to use "CALLED ON NULL INPUT" instead.
I'd appreciate some enlightenment on this point.
--
Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare