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 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.
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact