How to resolve ERROR - SQL STATEMENT GREATER THAN 65530 BYTES

Document ID : KB000013488
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

After making changes to an existing query, this error is produced when it is run:

ERROR - SQL STATEMENT GREATER THAN 65530 BYTES
SQL STATEMENT EXCEEDS 65530 CHARACTER(S) 

How do we resolve this error?

Answer:

There is a limit to the size of a single query to be processed within CA Datacom, and this limit of 65530 cannot be changed without some major architectural changes to the base CA Datacom/DB product. 

However, we have a few suggestions that might help you to make your query fit this limit: 

  1. We remove all trailing spaces from each line, but we do not remove leading spaces. Shifting your query text to the left as much as possible will reduce the size of your query; 
  2. If you are using table names with every column being referenced, you should consider correlations. For example if you are using authId.tblName.colName1, authId.tblName.colName2, authId.tblName.colName3, you can use the correlation name on the table reference, resulting in a shorter query: corrNm.colName1, corrNm.colName2, corrNm.colName3;
  3. If you are using a SELECT with a lot of OR conditions, you might be able to replace them with an IN clause. 
  4. If you have a SELECT with a large number of columns, you might replace them with a SELECT *. This will return more data than you need, but will allow a lot more room for the other clauses. 
Additional Information:

As always, please contact CA Technologies support for CA Datacom if you have further questions.