Re: Still more SQL date weirdness

On 31-May-2011 09:26 , James Lampert wrote:

I learned of this while still on vacation.

The thorny little problem of converting PF numeric dates into actual
date fields in a VIEW on the PF is still giving us trouble.

Given that the PFs involved have their date fields defined as
8-digit zoned decimal, in the format YYYYMMDD, and that the SQL VIEW
definition massages them with:

CREATE VIEW FOO/BAR (CDATE, . . . ) AS SELECT DISPLAYDAT(CDATE) . . .
FROM FOO/BAZ;

and DISPLAYDAT(i_date) is defined as:

create function FOO/DISPLAYDAT(i_date NUMERIC(8))
returns DATE
language sql deterministic not fenced set option datfmt=*ISO
begin
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
END

if I look at the VIEW in QuestView, the converted date field shows up
as a "Type-L" date field, with the correct value, and if I look at it
in SQUIRREL SQL, just fetching the records without manually entering
any SQL, I get the same.

BUT

If I do
SELECT * FROM FOO/BAR

in a STRSQL session, I get 2-digit years, with "++++++++" for any
dates that can't be displayed as 2-digit years, and if I plug the
same into SQUIRREL, I get nothing at all.

If I do
SELECT *
FROM FOO/BAR
WHERE ACCOUNT_ID = '100501'

in STRSQL, I get the "++++++++" again, while in SQUIRREL, I get the
expected ISO date, with "Warning: [SQL0181] Value in date, time, or
timestamp string not valid."

And if I do
SELECT *
FROM FOO/BAR
WHERE CDATE>= DATE('2011-01-01') AND CDATE < DATE('2011-03-01')

STRSQL gives me "Query cannot be run. See lower level messages,"
with "Syntax of date, time, or timestamp value not valid." apparently
being thrown *within* DISPLAYDAT, while SQUIRREL and BIRT (the latter
being the whole raison d'etre for this silly-go-round) produce
similar errors.

However, if I do the same thing with 2-digit years in the WHERE
clause (at least in STRSQL and SQUIRREL), it seems to work.

Can anybody shed any light on this?

Because the UDF does not have a HANDLER for any conditions which might arise [aside from the one specific case of a zero-value handled by an early-exit RETURN clause], any other numeric value which similarly does not represent a valid date value [as expressed in the TIMESTAMP as a 14-digit string of digits] will likely encounter an SQLSTATE of '01534' [or '22007'?]. Have all conditions of invalid numeric values [other than zero] been corrected in the CDATE NUMERIC(8) column of the physical file? While at first glance 20100229 might look valid, the DATE('20100229000000') will not evaluate to a date value because the 29-Feb-2010 is not a valid date, and although 99999999 might have 9999 as a valid\supported year, the month and day values as 99 would be problematic.

If selection is to be performed for any row for which the function of the column as [cast to] date value can not be evaluated [i.e. any string which does not make a valid 14-digit TIMESTAMP string], the query fails at that point. With a defaulted OPTIMIZE FOR 24 ROWS [or similar; e.g. *FIRSTIO for OPNQRYF] for STRSQL when using output to *DISPLAY, a query may be able to retrieve enough rows with selection before any failed expressions are encountered while determining if the row should be included, thus the query can produce results until any invalid date expression fails to be evaluated. In the displayed report, if the first block of rows can be presented without any errors, then failing may occur only when paging to[ward] any row with the invalid numeric CDATE value.

Perhaps test all queries using OPTIMIZE FOR ALL ROWS plus fetch every row in testing and\or change the SQL UDF to return NULL [e.g. on the specific SQLSTATE or any SQLEXCEPTION]. Perhaps adding after the BEGIN:
declare continue handler for SQLEXCEPTION return NULL;

Regards, Chuck