Re: SQL extracts of a DSPOBJD *outfile - dates

On 20 Dec 2012 14:04, Gqcy wrote:

<<SNIP>>
I am trying to create your "objd_ts" function
in all SQL, but am having trouble...

1) I need a CASE or IF for the century test,..
2) "SET RESULT =" keeps saying that "=" is invalid...
<<SNIP>>

No reason to use SET if the value can be directly RETURNed. FWiW I would also avoid the token RESULT as an identifier; lest its reference is only delimited, such as "RESULT". See the following [which will mindlessly turn any error into a NULL, although all but a bogus leap date should not be an issue for DSPOBJD output... else the DSPOBJD probably has a defect]:

<code>

drop function objd_dt
;
create function objd_dt
( cen char /* Century digit 0=19, 1=20, etc. */
, dat char(6) /* MMDDYY date, or blank */
) returns date
language sql
deterministic reads sql data
no external action returns null on null input
allow parallel
set option dbgview=*SOURCE, datfmt=*ISO
begin
declare EXIT HANDLER for SQLEXCEPTION return NULL;
return case when dat='' or cen='' then null
else decimal( cen + 19 , 2 ) concat
right( dat, 2 ) concat '-' concat
left( insert( dat, 3, 0, '-'), 5 )
-- :: A little less obvious, but less complex:
-- else decimal( cen + 19 , 2 ) concat
-- right( dat, 2 ) concat left( dat, 4)
-- concat '000000'
-- :: v5r3 required wrapping above in DATE()
end;
end
;
drop function objd_ts
;
create function objd_ts
( cen char /* Century digit 0=19, 1=20, etc. */
, dat char(6) /* MMDDYY date, or blank */
, tim char(6) /* HHMMSS time, or blank */
) returns timestamp
language sql
deterministic reads sql data
no external action returns null on null input
allow parallel
set option dbgview=*SOURCE, datfmt=*ISO, timfmt=*ISO
begin
declare EXIT HANDLER for SQLEXCEPTION return NULL;
return
case when dat='' or tim='' or cen='' then null
else timestamp( decimal( cen + 19 , 2 ) concat
right( dat, 2 ) concat '-' concat
left( insert( dat, 3, 0, '-'), 5 )
, insert( insert( tim , 5, 0, '.'), 3, 0, '.') )
-- A little less obvious, but shorter:
-- else decimal( cen + 19 , 2 ) concat
-- right( dat, 2 ) concat left( dat, 4) concat tim
end;
end
;

</code>