How to call a external table which is inside a schema in Connect SQL
Hi All,
I have to call a external DB table as below. I am getting error as invalid identifier.
SELECT col1,col2 FROM TABLE(xxx_dta_base.PKG_USR_xxx.get_disp_dta(12345,TO_DATE('05/20/2024','MM/DD/YYYY')))
Accepted Solution
Updated: 21 Aug 2024 4:37 EDT
IN
Pegasystems Inc.
CA
Did you run the exact query from SQL from your SQL server to ensure that this issue is related to the query or connect-sql?
IN
@RameshSangili I am using this query in Pega connect sql rule and error says invalid identifier "xxx_dta_base.PKG_USR_xxx.get_disp_dta"
Pegasystems Inc.
CA
To isolate the issue - Did you check the same query works fine if you execute in SQL server or DB instance?
Is this an issue only with connect-SQL rule?
IN
@RameshSangili i executed this in DB directly its working, only from connect sql i have issue.
Pegasystems Inc.
GB
@Pavanir6 if the product is not behaving as documented and you can replicate the issue consistently feel free to log a support issue via the MSP
Before you do, please check the below:
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
To call an external table inside a schema using Connect SQL in Pega, you need to ensure that the schema name is correctly specified in the Connect-SQL rule. If you are encountering an 'invalid identifier' error, it might be due to the schema name not being correctly referenced in the Connect-SQL rule. Here are the steps to follow:
1. **Specify the Schema Name**: Ensure that the schema name is explicitly mentioned before the table name in the Connect-SQL rule. For example, use `schema_name.table_name`.
2. **Database Table Rule**: Verify that the database table rule is correctly configured to map the class to the external table. This includes specifying the correct schema and table name.
3. **JDBC URL**: Ensure that the JDBC URL in the database rule is correctly formatted to include the schema. For example, `jdbc:postgresql://<hostname>:<port>/<databasename>?currentSchema=schema_name`.
@Pavanir6 if the product is not behaving as documented and you can replicate the issue consistently feel free to log a support issue via the MSP
Before you do, please check the below:
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
To call an external table inside a schema using Connect SQL in Pega, you need to ensure that the schema name is correctly specified in the Connect-SQL rule. If you are encountering an 'invalid identifier' error, it might be due to the schema name not being correctly referenced in the Connect-SQL rule. Here are the steps to follow:
1. **Specify the Schema Name**: Ensure that the schema name is explicitly mentioned before the table name in the Connect-SQL rule. For example, use `schema_name.table_name`.
2. **Database Table Rule**: Verify that the database table rule is correctly configured to map the class to the external table. This includes specifying the correct schema and table name.
3. **JDBC URL**: Ensure that the JDBC URL in the database rule is correctly formatted to include the schema. For example, `jdbc:postgresql://<hostname>:<port>/<databasename>?currentSchema=schema_name`.
4. **Environment Configuration**: Check if there are any environment-specific configurations that might be causing the issue. Sometimes, what works in one environment (e.g., development) might not work in another (e.g., UAT) due to differences in configuration.\n\nIf you follow these steps and still encounter issues, you might need to hard-code the schema name in the Connect-SQL rule as a temporary workaround, as mentioned in the discussion below.
Getting error while saving context dictionary, Pega Customer Decision Hub
Accepted Solution
Updated: 21 Aug 2024 4:37 EDT
IN