All Forums Database
ronirwin 4 posts Joined 02/12
15 May 2013
SUBSTR without explicitly coding the length parm

Hello I'm have a query that's taking a Failure 2805 ENTDW_IN_PRT_ACV2IN_PRT_ACV_SP:Maximum row length exceeded in ..... error.   Bascially, I have eight VARCHAR(4000) columns in the table which is what's causing the issue.   Not all of the 4000 bytes will be fully popualted at any point in time.  Therefore, I'm doing a substr for the length of the col itself using the CHARACTER_LENGTH function.  Such as

SUBSTR(PRIOR_INCIDENT_OF_CAN_TXT,1,CHARACTER_LENGTH(PR IOR_INCIDENT_OF_CAN_TXT))
What I've found is that if I explicitly set the length parameter of the SUBSTR to a value that I know is longer than the number of data characters in the column it works great.  However, if I code the statement above it won't work.  I've also tried every conceivable combination of the string manipulation and I can't get it to work unless I explicitly code the length.  I've even tried setting the length into a variable outside the query and using the variable in the SUBSTR and it still won't work.
Do you have any suggestions that will get the SUBSTR to work without explicitly coding the value?

ulrich 816 posts Joined 09/09
15 May 2013

I can't reproduce the issue.


select top 100
       substring(myvar from 4),
       substr(myvar,4),
       substr(myvar,14,CHARACTER_LENGTH(myvar))
from mytbl;

but as you can see above you don't need to specify the thrid parameter for substr and substring - check the different syntax in the docu.
So I guess this is what you are looking for?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ronirwin 4 posts Joined 02/12
16 May 2013

Thanks so much for responding, I appreciate it.  It's tough to duplicate because the sum total of the column lengths have to exceed the max size that TD allows before you will get the error.  I was using the SUBSTR function to reduce the total size enough to slip under the limit.
In any case, I do realize that there are multiple variations of the substr, and as you correctly pointed out, the length isn't even mandatory.  I just didn't understand that if you explicity code the length parameter to reduce the size of the column it would accept just fine.  However, if you code the length such that it uses the CHARACTER_LENGTH of the same column it doesn't seem to understand (even though the length of the column that the CHARACTER_LENGTH translates is the exact same value as what was "hard coded").  

dnoeth 4628 posts Joined 11/04
16 May 2013

It's exceeding the maximum row size because the column is defined as LATIN and your session character set is UNICODE and/or you ORDER BY some of those columns. The calculation is based on the possible, but not the actual size, if you SUBSTRING a VRACHAR(4000) the possible max length is still 4000.
This is a know pproblem, which will be fixed in TD14.10 which introduces 1 MB spool rows.
Dieter

Dieter

ronirwin 4 posts Joined 02/12
20 May 2013

Thanks for the explanation

You must sign in to leave a comment.