IBM DB2: SQL query with multiple Join

Question

I need to migrate a series of scripts with some queries that execute a lot of joins on multiple tables in order to have the values of a registry.

Unfortunately I can not handle these queries as I get an error on the sql:

SQL0203N A reference to the "INDEX_NOREL" column is ambiguous.

The database used is IBM DB2 and what puzzles me is that the client confirms that the query should work.

Does anyone know how I could modify this query to make it work properly?

I'm doing some tests but I do not know DB2 and I'm not very experienced in SQL. I understand that this column is ambiguous (had the same name in each tables) but if i try to rename the column i obtain an error of wrong context...

Here is the query:

SELECT
(CASE WHEN A.INDEX_FATT = 0 THEN A.INDEX_CL ELSE COALESCE(B.INDEX_CL, 0) END) AS INDCLI,
(CASE WHEN A.INDEX_FATT = 0 THEN A.INDEX_AP ELSE COALESCE(B.INDEX_AP, 0) END) AS INDP,
IND_NM002, IND_NM001, IND_DT001
FROM ICMADMIN.table_001 A
INNER JOIN ICMADMIN.table_002 ON INDEX_NOREL = A.INDEX_NOREL 
LEFT OUTER JOIN ICMADMIN.table_001 B ON B.VT2CTO_NOCTO = A.INDEX_FATT 
WHERE A.INDEX_NO=31 WITH UR FOR FETCH ONLY;

Qualify all columns: ... INNER JOIN ICMADMIN.table_002 ON ICMADMIN.INDEX_NOREL = A.INDEX_NOREL ... — jarlh

codeLover codeLover · Accepted Answer · 2018-08-08T08:53:56

The issue is probably due to the below clause:

INNER JOIN ICMADMIN.table_002 ON INDEX_NOREL = A.INDEX_NOREL

Here INDEX_NOREL on the left side is not accompanied by any table alias. Since ICMADMIN.table_001 and ICMADMIN.table_002 both have the column named INDEX_NOREL, that is why you are facing this issue. Just create an alias for ICMADMIN.table_002 and accompany INDEX_NOREL by that alias it will work.

   FROM ICMADMIN.table_001 A
    INNER JOIN ICMADMIN.table_002 C ON C.INDEX_NOREL = A.INDEX_NOREL

IBM DB2: SQL query with multiple Join

1 Answers