3798: A column or character expression is larger than the max size.

Database
Enthusiast

3798: A column or character expression is larger than the max size.

Hi all!

I am trying to vertically concatenate all the txt messages by id into one msg. I have defined the msg as varchar(64000).
When I try to run the query below , even after trimming the spaces in msg, it gives me an error -A column or character expression is larger than the max size.

SELECT sq1.id
, MAX(CASE WHEN sq1.flag = 1 THEN msg ELSE ' ' END ) ||
MAX(CASE WHEN sq1.flag = 2 THEN ', ' || msg ELSE ' ' END ) ||
MAX(CASE WHEN sq1.flag = 3 THEN ', ' || msg else ' ' END ) AS ConcatenatedRows
FROM
(
SELECT id , trim(both from msg) as msg
,RANK() OVER (PARTITION BY id
ORDER BY id ) AS Flag
FROM TMptable
) sq1
GROUP BY 1

So if my table is initially like this:
1 abc
1 def
2 efg
2 ghi

Then my output shd be like this, where 'abc' 'def' are in the same record but different lines, i need kind of a line break.
1 abc
def
2 efg
ghi

I know my query does not do this, but my ultimate gooal is this.

Thanks!

5 REPLIES
Enthusiast

Re: 3798: A column or character expression is larger than the max size.

In Teradata, maximum column length supported is around 64K. Declare it as CLOB
Junior Contributor

Re: 3798: A column or character expression is larger than the max size.

1.
The concatenated string must be less than 64000 chars.
And this is not actual, but possible chars, so you better change the definition of "msg" to an appropriate size.
Or cast it like
SELECT id , cast(trim(both from msg) as varchar(100)) as msg

2.
Of course you could use a line break (for Windows '0D0A'XC) instead of a blank, but why do you want that?

Dieter
Enthusiast

Re: 3798: A column or character expression is larger than the max size.

Yes, My Msg is much less than 64000 chars. And I cannot trim use varchar(100), because I dont know how much the length would be. Any suggestions?
I need a line break to do some basic formatting in the output which will be used by the users.
Enthusiast

Re: 3798: A column or character expression is larger than the max size.

Dieter, you said you could use a line break (for Windows '0D0A'XC) instead of a blank, but why do you want that? - I need some kind of formatting in my Message so that it is readable. So if I insert '0D0A' whereevr I want a line break, will that line start on a different line in my Message column?
Junior Contributor

Re: 3798: A column or character expression is larger than the max size.

If you concat three lines the maximum length for message is approx. 21000, which should be enough.

It's not '0D0A' but '0D0A'XC
XC means the previous string is in hex and it's characters.

select 'bla' || '0D0A'XC || 'bla';
Caution, this will fail in Bteq, as this tool removes all linebreaks.

Dieter