SQLCODE -34 and SQLSTATE 42704 using Subquery in HAVING clause

Products

• Datacom
• Datacom/DB
• Datacom/AD

Issue/Introduction

When running a query with a HAVING clause and using a subquery, it is possible to receive an unexpected error:

___________SQL ERROR SUMMARY START___________                                
                                                                             
SQLCODE = -34, SQLSTATE=42704                                                
MESSAGE = ILLEGAL CORRELATION NAME                                           
____________SQL ERROR SUMMARY END____________                                

The error also does not indicate what the correlation name is that is illegal, leading to confusion.

 

Environment

Component : CA DATACOM SQL

 

Cause

The query itself may not be structured correctly and a column used in the HAVING Subquery might be missing from the GROUP BY.

 

Resolution

Check the columns in the HAVING clause and be sure that all of them are also listed in the GROUP BY clause.

The error message here is misleading, because of an error in the HAVING clause dealing with a column using a Correlaton. This error could be thought of as an invalid correlated reference. Since the column itself is in error (because it is missing from the GROUP BY), having a correlation on it is therefore also an error.

 

Additional Information

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