SQLCODE = -33, SQLSTATE=42702 MESSAGE = AMBIGUOUS COLUMN REFERENCE: TNC1

Products

• Datacom
• Datacom/AD
• Datacom/DB

Issue/Introduction

To illustrate this error, consider this query:

CREATE TABLE T21300 (
    C1 INT,          
    C2 INT           
);                   
                     
SELECT               
    T1.C1 AS T1C1,   
    T2.C1 AS T2C1,   
    C1 AS TNC1       
FROM                 
        T21300 T1    
    INNER JOIN       
        T21300 T2    
    ON               
        T1.C1 = T2.C1
;                    
                     
DROP TABLE T21300;   

In the query, this line was missing the table qualifier for C1 column:
C1 AS TNC1

After running the query, this ambiguity error displayed:

 ___________SQL ERROR SUMMARY START___________ SQLCODE = -33, SQLSTATE=42702 MESSAGE = AMBIGUOUS COLUMN REFERENCE: TNC1 ____________SQL ERROR SUMMARY END____________

It was C1 column that would not qualified but instead TNC1 name was picked up in the error text.

Environment

Release : 15.0

Release : 15.1

Component : Datacom/AD

Component : Datacom/DB

Cause

The column name C1 should have been in the error message.  The AS name TNC1 is only for applications like DBSQLPR to give a heading name in a report.  And perhaps give a name in a CREATE VIEW.

Resolution

LU11199 solution was created to fix this issue.

After the solution was applied, the previous query was ran again, and this time the SQL ambiguous error message displayed the correct text, referencing the correct column. In this example was C1:

 ___________SQL ERROR SUMMARY START___________ SQLCODE = -33, SQLSTATE=42702 MESSAGE = AMBIGUOUS COLUMN REFERENCE: C1 ____________SQL ERROR SUMMARY END____________

 

 

 

Additional Information

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