Archives of the TeradataForum

Message Posted: Thu, 26 Jun 2003 @ 13:46:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello List,

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).

Best Regards,

Joe Fuller
Sr Data Warehouse Consultant, DBA
Teradata Professional Services

Advanced Teradata Certified Professional
Teradata Certified Designer

	<Prev	Next>		<<First	<Prev	Next>	Last>>