UPDATE/DELETE statements in the subquery of an IN clause may fail with an error in TIBCO Data Virtualization.
Article ID: KB0074860
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | All Supported Versions |
Description
Executing a query containing UPDATE/DELETE statement in the sub query of an IN clause in TDV Studio may return the below error message:
A system exception has occurred.
at AnonymousProcedure (line 3)
caused by: Too many values returned for IN predicate in UPDATE / DELETE statement. [qengine-1900361] [script-1900011]
Too many values returned for IN predicate in UPDATE / DELETE statement.
Root Cause:
The error message is returned if the number of records being UPDATED/ DELETED in the IN clause are greater than the number set for the configuration "In Clause Limit For SubQuery In Update And Delete". The configuration controls the maximum number of records that TDV can handle in an 'IN Clause' after executing the subquery specified using 'Update' and 'Delete' commands.
A system exception has occurred.
at AnonymousProcedure (line 3)
caused by: Too many values returned for IN predicate in UPDATE / DELETE statement. [qengine-1900361] [script-1900011]
Too many values returned for IN predicate in UPDATE / DELETE statement.
Root Cause:
The error message is returned if the number of records being UPDATED/ DELETED in the IN clause are greater than the number set for the configuration "In Clause Limit For SubQuery In Update And Delete". The configuration controls the maximum number of records that TDV can handle in an 'IN Clause' after executing the subquery specified using 'Update' and 'Delete' commands.
Issue/Introduction
UPDATE/DELETE statements in the subquery of an IN clause may fail with an error in TIBCO Data Virtualization.
Environment
All supported Operating Systems
Resolution
Follow the below steps to resolve the issue:
1. Open TDV Studio.
2. Navigate to Administration-->Configuration-->Server-->SQL Engine-->Overrides-->In Clause Limit For SubQuery In Update And Delete.
The default value for this configuration is set to 10,000. Increase the value as per the query requirement.
NOTE: Make sure to not set this configuration to a very high value as the back end data base may have it's own limit set. This configuration will control the limit at the TDV level.
3. Apply the changes and close the configuration window.
4. Execute to query to validate the solution.
1. Open TDV Studio.
2. Navigate to Administration-->Configuration-->Server-->SQL Engine-->Overrides-->In Clause Limit For SubQuery In Update And Delete.
The default value for this configuration is set to 10,000. Increase the value as per the query requirement.
NOTE: Make sure to not set this configuration to a very high value as the back end data base may have it's own limit set. This configuration will control the limit at the TDV level.
3. Apply the changes and close the configuration window.
4. Execute to query to validate the solution.
Feedback
Yes
No
Powered by
