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.
Accepted Solution
Optus
AU
Hi,
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.
PEG
GB
I'm only really expecting to see the call to pr_read_from_stream if the underlying column hasn't been optimized.
Can you please check the two table structures on your 7.1.8 and 7.2.1 instances and verify if this is the case.
Infosys
US
You're correct, that column in 7.2.1 isn't exposed, but it is in 7.1.8.
Can I change the sql function somehow or do I just need to have that column optimized?
Thank you
Accepted Solution
Optus
AU
Hi,
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.
Infosys
US
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.
Thanks.