CODEWITHSUNDEEP
sqldatejoindb2inner-join
shubham sinha
shubham sinha

Reputation: 49

How to inner join two tables using DATE clause in DB2

SELECT d.local_reference_no
FROM   emcs_ct.t_draft_movement_snapshot d
INNER JOIN emcs_ct.t_movement_snapshot M
      ON M.local_reference_no = d.local_reference_no
WHERE  created_date > '2019-06-18';

CONSOLE LOG : SQL Error [42702]: A reference to column "CREATED_DATE"
is ambiguous.. SQLCODE=-203, SQLSTATE=42702, DRIVER=4.7.85

Please suggest something

Upvotes: 1

Views: 2178

Answers (2)

Umesh Aawte
Umesh Aawte

Reputation: 4690

“Ambiguous column name” is the column that is present in more than one table in your sql statement. Its good to refer table name along with column which you want to refer in your clause.

Its is good that you used alias in the sql.

If the field CREATED_DATE is from table T_DRAFT_MOVEMENT_SNAPSHOT use following statement

SELECT d.LOCAL_REFERENCE_NO FROM EMCS_CT.T_DRAFT_MOVEMENT_SNAPSHOT d INNER JOIN EMCS_CT.T_MOVEMENT_SNAPSHOT M ON M.LOCAL_REFERENCE_NO = d.LOCAL_REFERENCE_NO WHERE d.CREATED_DATE > '2019-06-18';

Else use following

SELECT d.LOCAL_REFERENCE_NO FROM EMCS_CT.T_DRAFT_MOVEMENT_SNAPSHOT d INNER JOIN EMCS_CT.T_MOVEMENT_SNAPSHOT M ON M.LOCAL_REFERENCE_NO = d.LOCAL_REFERENCE_NO WHERE M.CREATED_DATE > '2019-06-18';

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

SELECT d.LOCAL_REFERENCE_NO 
FROM EMCS_CT.T_DRAFT_MOVEMENT_SNAPSHOT d 
INNER JOIN EMCS_CT.T_MOVEMENT_SNAPSHOT M ON M.LOCAL_REFERENCE_NO = d.LOCAL_REFERENCE_NO 
WHERE CREATED_DATE > '2019-06-18';
      ^

Since the CREATED_DATE column may exists in both the tables, so in the WHERE clause it doesn't know from which table's CREATED_DATE it need to use.
So you need to specify the CREATED_DATE is from which table, like d.CREATED_DATE or M.CREATED_DATE. It will solve your issue.

Upvotes: 1

Related Questions