Re: Still more SQL date weirdness

[Luis Rodriguez <luisro58@xxxxxxxxx>]

James,

I think that the appropriate handler would be an EXIT handler. Something
like this:

Declare Exit Handler For SQLException Return date('99991231000000');

Also, Shouldn't it be (i_date NUMERIC(8, 0) ?

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Wed, Jun 1, 2011 at 2:58 PM, James Lampert <jamesl@xxxxxxxxxxxxxxxxx>wrote:

> I think I have error dates handled now:
>
> Where @@@@@@@@@@ is a placeholder for the library, in the RPG program
> that generates the SQL source member for the UDF, I now have with the
> new lines marked):
>
>  DROP FUNCTION @@@@@@@@@@/DISPLAYDAT;
>  create function @@@@@@@@@@/DISPLAYDAT(i_date NUMERIC(8))
>    returns DATE
>   language sql deterministic not fenced  set option datfmt=*ISO
>  begin
>    declare continue handler for sqlexception            **NEW**
>
>                      return date('99991231000000');    **NEW**
>
>   if i_date = 0 then return date('00010101000000'); end if;
>   return date(digits(i_date) concat '000000');
>  END
>
> What I know about handlers wouldn't buy you a cup of coffee, so is there
> any reason why this handler would create a problem? Am I correct in my
> understanding that a handler's scope is confined to the innermost BEGIN
> block in which it resides? That it can't leak out and wreak havoc with
> exceptions elsewhere?
>
> --
> JHHL
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.