Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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.

Suggest Edit

Published March 14, 2018 - Updated October 8, 2020

Did you find this content helpful? Yes No

100% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us