Support Article
Report Definition can't join External Source table
SA-45668
Summary
Create a Pega Report Definition and on the Data Access tab configure to join an external table that is not in Pega environment.
Saving the report works fine but running the report will display an error.
Error Messages
Error:
** An error occurred on executing the query for the report definition - There was a problem getting a list: code: 942 SQLState: 42000 Message: ORA-00942: table or view does not exist DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist | SQL Code: 942 | SQL State: 42000
Steps to Reproduce
1. Create a Pega Report Definition on any existing work table.
2. Configure the Data Access Tab to join an external DB table.
3. Run Report.
Root Cause
A third-party product issue.
In order to query across databases one must establish the link at the database level.
Resolution
Here’s the explanation for the reported behavior:
In order to perform a join across databases with Oracle the DBA would need to setup a database link and create a synonym (or view) to hide the fact that it is in a remote database so the query syntax is the same as if the tables were in the same database.
The database user must be granted appropriate permissions to query the external table.
Published March 14, 2018 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.