SUBSTR without explicitly coding the length parm

Database

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(PRIOR_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?

4 REPLIES
Supporter

Re: SUBSTR without explicitly coding the length parm

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?

Re: SUBSTR without explicitly coding the length parm

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

Junior Contributor

Re: SUBSTR without explicitly coding the length parm

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

Re: SUBSTR without explicitly coding the length parm

Thanks for the explanation