While using SQL JOINS , we sometime may face DUPLICATE PRIMARY KEY ERROR. This error may especially come while tables containing history are included in JOIN conditions. So while using SQL Joins, we must take care of this problem. Let us look into this problem in more detail:
Suppose we have a table which is maintaining history of all the CUSTOMERS. And we do a join with this table with some other table on the basis of CUST_ID (key column in this case) . Since the table is maintaining history of CUSTOMERS, so it will be having multiple entries for same CUST_ID. So if the join condition is on CUST_ID, it will result in one to many mapping and the result set will also have multiple entries for same CUST_ID. Now if the target is having CUST_ID as primary key, it will throw UNIQUE PRIMARY KEY violation error. So how to avoid such situation ? The easiest way of overcoming this issue is to fetch the latest entry of CUST_ID from the history table. So in that case, it will be fetching only one instance of each CUST_ID even if multiple entries exists.
Now we will see how we can implement this using the SQL in Teradata. If the table is maintaining history in the table then it must be having a date column which will be useful in maintaining history. So you can put a condition on the date column to fetch only the latest row when multiple exists for same key. Let us see how you can implement this check in Teradata:
SELECT CUST_ID , CUST_NAME,CUST_CITY FROM CUSTOMERS QUALIFY ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY DAT_UPD DESC)=1;
This query will fetch only one instance of each row. So you can add this clause at the end of any SQL query involving tables with multiple entries for same key. This way you can avoid UNIQUE PRIMARY KEY violation error.