Posted: 19 Jun 2017 16:52 EDT Last activity: 22 Jun 2017 14:57 EDT
TO_CHAR Causing SQL 1722 Error in Pega 7.2.1
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"
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.
When you upgrade, the DB structure for data (After upgrade) must be same as source DB (Before upgrade). Please double check the DADT instances of underlying RD class to check for correct table mapping, if it is same in both the environments try to find the root cause of why the column not exposed in destination version.
The external table used for the RD is missing in all of our 7.2.1 instances, but is present in all 7.1.8 instances.
Some of the tables must have been left out or remapped or something during the update. I guess we can fix this issue by just reoptimizing the column, but we'll have to figure out why/how the db structure was changed to make sure no other issues were created.