On 01-Jun-2011 11:41 , James Lampert wrote:
I had a value of 00000001 in one of the zoned-date fields of the
Obviously, I need to handle that case.
Yesterday and this morning, Chuck, Rob, et al. said something
about a "handler." Not quite sure how that would work.
Hmm. I see that "SQL for Dummies" has something on it.
I'll let you know if I need more help with this.
CRPence wrote earlier:
Perhaps adding after the BEGIN:
declare continue handler for SQLEXCEPTION return NULL;
So a revision to the original function, given Rob's assumption about
an unstated desire to avoid the database NULL value, then add the
HANDLER after the "begin" for the existing routine source; then DROP
FUNCTION and CREATE FUNCTION anew with that modified source:
create function FOO/DISPLAYDAT(i_date NUMERIC(8))
language sql deterministic not fenced set option datfmt=*ISO
declare continue handler for SQLEXCEPTION return date('00010101000000');
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
Because the UDF is so "slim", handling any SQL exceptions in that
manner may be acceptable, as compared with only handling the specific
SQLSTATE value [or values] for the possible exceptions in date handling.
I think the SQLSTATE of '01534' should cover all invalid date syntax
possibilities in that UDF, although I alluded in my prior post about the
SQLSTATE of '22007' being another possible effect. But because I would
not want to waste my time figuring out specifically which SQLSTATEs to
handle, that is why I mentioned just handling SQLEXCEPTION. I also
would want to use the NULL value [or a different value than the
zero-date representation as Rob also alluded] in order to ensure knowing
the difference between the bad data case and the apparently acceptable