Search code examples
sqldatejoindb2inner-join

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

Solution

  • “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';