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?

--
JHHL

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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 [javascript protected email address].