Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator.

Analytics

Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator.

Hi,

I have been trying to compare results of two queries using minus but i am getting the below error

Failure 2805 Maximum row length exceeded in abc

The query i'm using is

select * from abc

minus

select * from bcd

;

I found that UNION ALL works fine, but other operators like MINUS,UNION and INTERSET doesn't work.

The length of all the columns combined in both the tables doesn't exceed 5000.

Thanks in advance.

Sudeep.

5 REPLIES

Re: Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator.

Sudeep:

You say the length of all the columns combined in both the tables doesn't exceed 5000, whereas Teradata says the contrary.

You don't provide the real tables definitions, the real queries or the real explains... sorry I'd rather believe Teradata.

Cheers.

Carlos.

Re: Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator.

It's probably the length of the sort key that pushes the result spool row length over the limit. UNION ALL works because it doesn't require a sort to remove duplicates.

Re: Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator.

Hi Carlos,

The table defination is like:

CREATE MULTISET TABLE abc ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      ID INTEGER TITLE '  Identifier' NOT NULL,

      a1 VARCHAR(4000) ,

      a2 VARCHAR(4000)  ,

      a3 VARCHAR(4000)   ,

      a4 VARCHAR(4000)  ,

      a5 VARCHAR(4000)  ,

      a6 VARCHAR(4000)  ,

      a7 VARCHAR(4000) ,

      a8 INTEGER TITLE  'Emp_ID' NOT NULL,

      a9 INTEGER TITLE 'Salary',

      a10 TIMESTAMP(0) )

UNIQUE PRIMARY INDEX XUPI_abc ( ID );

and when i try to find the max length of concatenated data with char_length the length is within 5000.

Re: Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator.

I think length of the sort key can be reason for the error.

Re: Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator.

Sudeep:

Can you post the result of EXPLAIN with VERBOSEEXPLAIN?

Cheers.

Carlos.