; return false' class='button' title='Print page'><img src='http://cache-02.cleanprint.net/media/pfviewer/images/CleanPrintBtn_black.png){kind=link}
Usually you want a quick easy response to the problem that you are searching for the solution to on the web, right? Well here are two common reasons (with example code) why you may be receiving this error:
1.) On your join statement, your ON or AND statements are asking to join columns on a different table other than the two tables that you are originally intending to join.
Example:
SELECT A.CUST_ID, B.CUST_NAME, C.CUST_ADDRESS
FROM TD.CUSTOMER_REF A
LEFT JOIN TD2.CUST_INFO B
ON B.CUST_ID = A.CUST_ID
AND B.CUST_REF = C.CUST_REF /*this should be A.CUST_REF as opposed to C.CUST_REF*/
LEFT JOIN TD3.CUST_SHIPPING C
ON C.CUST_ID = A.CUST_ID
AND C.CUST_REF = A.CUST_REF
The bolded line is written incorrectly because although TD3.CUST_SHIPPING does have a CUST_REF column, it should not be a join criteria for the second left join.
2.) Another scenario where this can occur is when you join a table using a where clause and a join. I can’t give you the specifics as to why it works the way it does, but when your second table reference preceeds the left join you are likely to get this error. The rule of thumb that I have followed and had success with is to have the join follow the table you plan to join on. This is likely to prevent other errors too.
See the syntax examples below for correct and incorrect way of joining tables using joins and where clauses.
Example of incorrect way to use Joins and Where clauses
SELECT A.CUST_ID, B.CUST_NAME, C.CUST_ADDRESS
FROM TD.CUSTOMER_REF A,
TD3.CUST_SHIPPTING C
LEFT JOIN TD2.CUST_INFO B
ON B.CUST_ID = A.CUST_ID
WHERE C.CUST_ID = A.CUST_ID
AND C.CUST_REF = A.CUST_REF
Example of correct way to use Joins and Where clauses
SELECT A.CUST_ID, B.CUST_NAME, C.CUST_ADDRESS
FROM TD.CUSTOMER_REF A
LEFT JOIN TD2.CUST_INFO B
ON B.CUST_ID = A.CUST_ID
TD3.CUST_SHIPPTING C
WHERE C.CUST_ID = A.CUST_ID
AND C.CUST_REF = A.CUST_REF
As you can see, the placement of the line in bold is the difference between these queries working (or not). As a side note, the query techniques mentioned above have been tested on both the Teradata and DB2 zOS environments and have proven successful. Other databases such as MySQL may be more lax and accept your SQL as is.