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) . . .
and DISPLAYDAT(i_date) is defined as:
create function FOO/DISPLAYDAT(i_date NUMERIC(8))
language sql deterministic not fenced set option datfmt=*ISO
if i_date = 0 then return date('00010101000000'); end if;
return date(digits(i_date) concat '000000');
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.
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
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
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
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?