Error: LOBs are not allowed to be hashed

Database

Error: LOBs are not allowed to be hashed

I did Google this and it talks about data lengths. We have 4 columns in one table that are 1500 varchar because the end users say they use EXCEL for logging updates on items and they need as much space as they can get. However, when I put the table into Access, I limited it to 1500 and am thinking that is too big for Teradata. So I actually removed those columns all together for the time being and refreshed my Tableau connection. I am still getting this error. The only other table that has a 1500 varchar is my program table because the program description for interventions are lengthy. The max length I found is 1333 if I remember correctly. The rest of the tables are normal. Is this telling me that I have to now tell the program managers there is a maximum number of characters that are allowed and say it is 500 and we are going to have to figure out a way to alter the descriptions of the programs to not exceed 500 or whatever the maximum can be?

Is there any articles out there that tell me how I can figure out maximum lengths. Not sure if it means maximum column length or maximum over all table lenth when added together or maximum lengths when you add all tables together. I thought I read somewhere something about 65000 but not sure what that was referring to.

6 REPLIES
N/A

Re: Error: LOBs are not allowed to be hashed

The maximum size for a VarChar is 64000 for Latin or 32000 for Unicode character set, so this is not causing a problem. DO a SHOW TABLE to check it's definiton.

This is from the Messages manual:

5690 LOBs are not allowed to be hashed.

Explanation: LOBs are not allowed to be hashed. Hashing is required for UNION, INTERSECT, MINUS, and DISTINCT queries containing LOB columns or for ORDER BY’s on LOB columns or for select query with SAMPLE clause specified on LOB columns.

Generated By: OPT modules.

For Whom: End User.

Remedy: Remove the LOB column and resubmit.

Re: Error: LOBs are not allowed to be hashed

can you paste the table ddl which gives this error? and possibly the sql too ?

Re: Error: LOBs are not allowed to be hashed

Can you please provide more details on this

Explanation: LOBs are not allowed to be hashed. Hashing is required for UNION, INTERSECT, MINUS, and DISTINCT queries containing LOB columns or for ORDER BY’s on LOB columns or for select query with SAMPLE clause specified on LOB columns.

Teradata Employee

Re: Error: LOBs are not allowed to be hashed

What sort of details?

  • The Teradata hash function does not accept LOBs as arguments; but
  • The Teradata implementation of certain operations depends on having a hash value - so LOBs cannot be used in those contexts

Re: Error: LOBs are not allowed to be hashed

How can we validate LOB's. 

We cant use UNION, INTERSECT, MINUS, and DISTINCT , do we have any alternate way to check. Please suggest.

Re: Error: LOBs are not allowed to be hashed

Hi Fred,

 

How to we check whether two different systems have the same hash algorithm or the function.If the two systems have different hash functions; how to restore the tables that have LOB'S

2. How to find the tables that contain LOB's?

 

Thanks in advance.