We are on PEGA 8.2.2. Our pega database is an oracle DB. For a workflow requirement, we have also connected to an external MSSQL database. Let's say database server name is '123'. In '123' MS Sql Db, there are multiple databases 'ABC' and 'DEF' on same server. We need to do some joins of tables across these 2 DBs and display in PEGA.
1. We configured these 2 databases as different data sources in IBM websphere console with same J2C authentication data as one db user has access to all DBs on '123' server.
2. Created 2 databases instance rules in PEGA and mapped to web console JNDI names
3. Created 2 classes and 2 database tables instances and mapped them to these 2 database instances along with schema name and table name
4. Created a connect sql rule and in the browse tab, doing join of these 2 tables.
When we run the query, PEGA is not able to understand the second table from the join. It works fine if we write query with either one of the 2 classes so, there is no issue with Db connections but if we do join across databases in sql query, it fails.
***Edited by Moderator Marissa to update platform capability tags****