Question

4
Replies
137
Views
Close popover
Wesley Cotterman (WesleyC6)
Infosys

Infosys
US
WesleyC6 Member since 2017 2 posts
Infosys
Posted: June 19, 2017
Last activity: June 22, 2017
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,
Close popover This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.