Question

4
Replies
114
Views
WesleyC6 Member since 2017 2 posts
Infosys
Posted: 3 years ago
Last activity: 3 years 3 months ago
Closed
Solved

TO_CHAR Causing SQL 1722 Error in Pega 7.2.1

Hi,

I'm working on an update from 7.1.8 to 7.2.1 and have run into an issue with a sql function used to format dates.

We have a search feature that includes a drop down of datetimes populated from a data page and report definition. The value of the drop down options is the datetime, but the display text is supposed to be a formatted date (MM/DD/YYYY). The report definition includes a column DateFormat [.RELEASEDATE] which is used for the display text, and DateFormat is just TO_CHAR([date],'MM/DD/YYYY').

When the report definition is run it throws code: 1722 SQLState: 42000 Message: ORA-01722: invalid number.

The database has only 20170615T040000.000 GMT and some null rows, and the code worked in 7.1.8

During debugging I noticed a significant difference in the generated SQL statements for the report definitions, so I assume the issue is somewhere in there.

In 7.1.8 the sql is:

SELECT DISTINCT "PC0".RELEASEDATE AS "RELEASEDATE" , TO_CHAR("PC0".RELEASEDATE, 'MM/DD/YYYY') AS "pyDateValue(1)" FROM SCHEMA.TABLE "PC0"

7.2.1 is:

SELECT DISTINCT SCHEMA.pr_read_from_stream('.RELEASEDATE', "PC0".pzInsKey, "PC0".pzPVStream) AS "RELEASEDATE" , TO_CHAR(SCHEMA.pr_read_from_stream('.RELEASEDATE', "PC0".pzInsKey, "PC0".pzPVStream), 'MM/DD/YYYY') AS "pyDateValue(1)" FROM SCHEMA.pr_other "PC0" WHERE "PC0"."PXOBJCLASS" = ?

I've tried everything I can think of and any help would be greatly appreciated.

Reporting
Moderation Team has archived post
Share this page LinkedIn