Archives of the TeradataForum
Message Posted: Thu, 26 Jun 2003 @ 13:46:13 GMT
I have a question about the HASHROW function. I am working on a project where a great many views are used. I would like to use the HASHROW function to generate a sort of "surrogate" key for a table with multiple columns in what will be it's primary key (there are some dupes, but that is a source system issue that is being worked). But, I made an interesting discovery, on a table with 3,000,000 rows:
Using the HASHROW function on the columns that should be unique...
SELECT COUNT(*) - COUNT(DISTINCT HASHROW(CH.ACCESS_METHOD_ID ,CH.CALL_START_DT ,CH.CALL_START_TM ,CH.CALL_ORIGINATING_NUM)) AS HASHDUPES FROM BI_DWV.CALL_HIST AS CH ; HASHDUPES 51,607
Now, doing a group by to see how many dupes...
SELECT COUNT(*) FROM (SELECT CH.ACCESS_METHOD_ID ,CH.CALL_START_DT ,CH.CALL_START_TM ,CH.CALL_ORIGINATING_NUM ,COUNT(*) AS MYCOUNT FROM BI_DWV.CALL_HIST AS CH GROUP BY CH.ACCESS_METHOD_ID ,CH.CALL_START_DT ,CH.CALL_START_TM ,CH.CALL_ORIGINATING_NUM HAVING COUNT(*) > 1) AS T Count(*) 21
Does anyone have any idea what is going on here? I am runnning V2R5
InfoKey InfoData RELEASE V2R.05.00.00.16 VERSION 05.00.00.16
I would have thought that the HASHROW function would pretty much guarantee uniqueness (except for the 21 dupes).
Advanced Teradata Certified Professional
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|