How to manipulate VARCHAR(32000) without getting Failed [9134 : HY000] Result exceeds the output arg

Database
Enthusiast

How to manipulate VARCHAR(32000) without getting Failed [9134 : HY000] Result exceeds the output arg

I am using TD Studio 15.12 with Teradata 15.10

 

I am trying count the number of times JOIN appears in the Request Text of a View written to DBC. I have used the approach of counting the number of characters in the RequestText and then substracting the number of chartacters in the RequestText after replacing 'JOIN ' with '', then dividing by the number of characters in 'JOIN '.

 

When I try to use it against the large views in DBC.TableTextV I get the error Failed [9134 : HY000] Result exceeds the output argument size limit. I have tried OREPLACE and REGEXP without success.

 

The manuals advise that these functions can process LATIN 32000 / UNICODE 64000 - RequestText in DBC.TablesTextV / DBC.TextTbl is VARCHAR(32000) CHARACTER

SET UNICODE. So I don't understand why it is saying it is exceeding argument size.

 

I have tried substringing the RequestText in 5000 characters but still get the same error.

 

Appreciate any pointers to resolve this - Thanks

 

Select DatabaseName, TableName, LineNo

,CHARACTER_LENGTH(trim(RequestText)) as NumberOfCharsInView

,(CHARACTER_LENGTH(substring(trim(RequestText) from 1 for 5000)) - CHARACTER_LENGTH(oREPLACE(substring(TRIM(RequestText) from 1 for 5000),'JOIN ',''))) / CHARACTER_LENGTH('JOIN ')

+ (CHARACTER_LENGTH(substring(trim(RequestText) from 5001 for 10000)) - CHARACTER_LENGTH(oREPLACE(substring(TRIM(RequestText) from 5001 for 10000),'JOIN ',''))) / CHARACTER_LENGTH('JOIN ')

+ (CHARACTER_LENGTH(substring(trim(RequestText) from 10001 for 15000)) - CHARACTER_LENGTH(oREPLACE(substring(TRIM(RequestText) from 10001 for 15000),'JOIN ',''))) / CHARACTER_LENGTH('JOIN ')

+ (CHARACTER_LENGTH(substring(trim(RequestText) from 15001 for 20000)) - CHARACTER_LENGTH(oREPLACE(substring(TRIM(RequestText) from 15001 for 20000),'JOIN ',''))) / CHARACTER_LENGTH('JOIN ')

+ (CHARACTER_LENGTH(substring(trim(RequestText) from 20001 for 25000)) - CHARACTER_LENGTH(oREPLACE(substring(TRIM(RequestText) from 20001 for 25000),'JOIN ',''))) / CHARACTER_LENGTH('JOIN ')

+ (CHARACTER_LENGTH(substring(trim(RequestText) from 25001 for 32000)) - CHARACTER_LENGTH(oREPLACE(substring(TRIM(RequestText) from 25001 for 32000),'JOIN ',''))) / CHARACTER_LENGTH('JOIN ')

 

As NumberOfJoins

from dbc.TableTextV

where 1=1

and databasename = 'DW_TEST'

and tablekind = 'V'

 

) S1

group by 1,2

order by CountOfJoins desc

 

 

sel DatabaseName, TableName, sum(NumberOfJoins) as CountOfJoins

from (

 

Select DatabaseName, TableName, LineNo

,CHARACTER_LENGTH(trim(RequestText)) as NumberOfCharsInView

,(CHARACTER_LENGTH(substring(trim(RequestText) from 1 for 5000)) - CHARACTER_LENGTH(REGEXP_REPLACE(substring(TRIM(RequestText) from 1 for 5000),'JOIN ','',1,0,'i'))) / CHARACTER_LENGTH('JOIN ')

 

--+ (CHARACTER_LENGTH(substring(trim(RequestText) from 5001 for 10000)) - CHARACTER_LENGTH(REGEXP_REPLACE(substring(TRIM(RequestText) from 5001 for 10000),'JOIN ','',1,0,'i|l'))) / CHARACTER_LENGTH('JOIN ')

--+ (CHARACTER_LENGTH(substring(trim(RequestText) from 10001 for 15000)) - CHARACTER_LENGTH(REGEXP_REPLACE(substring(TRIM(RequestText) from 10001 for 15000),'JOIN ','',1,0,'i|l'))) / CHARACTER_LENGTH('JOIN ')

--+ (CHARACTER_LENGTH(substring(trim(RequestText) from 15001 for 20000)) - CHARACTER_LENGTH(REGEXP_REPLACE(substring(TRIM(RequestText) from 15001 for 20000),'JOIN ','',1,0,'i|l'))) / CHARACTER_LENGTH('JOIN ')

--+ (CHARACTER_LENGTH(substring(trim(RequestText) from 20001 for 25000)) - CHARACTER_LENGTH(REGEXP_REPLACE(substring(TRIM(RequestText) from 20001 for 25000),'JOIN ','',1,0,'i|l'))) / CHARACTER_LENGTH('JOIN ')

--+ (CHARACTER_LENGTH(substring(trim(RequestText) from 25001 for 32000)) - CHARACTER_LENGTH(REGEXP_REPLACE(substring(TRIM(RequestText) from 25001 for 32000),'JOIN ','',1,0,'i|l'))) / CHARACTER_LENGTH('JOIN ')

As NumberOfJoins

from dbc.TableTextV

where 1=1

and databasename = 'DW_TEST'

and tablekind = 'V'

 

) S1

group by 1,2

order by CountOfJoins desc

 

2 REPLIES
Teradata Employee

Re: How to manipulate VARCHAR(32000) without getting Failed [9134 : HY000] Result exceeds the output

Actually the manual says the maximum length of a VARCHAR result value for OReplace() is 16000 characters for LATIN, and 8000 characters for UNICODE.  And the maxium result value for Regexp_Replace() is 16000, whether it's LATIN or UNICODE.  On the other hand, the regexp_* functions can also deal with 16 MB CLOBs, so if you could re-cast the string to CLOB it might work.  But why not just export these statements to a flat file and use grep -ci to count the occurrences of 'join'?

That reminds me - I once wrote a C UDF that does that.  I called it grepc(), even though it only worked with plain text, not regular expressions. (I guess fgrepc would be a better name.)  I would show you the (simple) source code, but you can still download it here.  (Note: ignore most of the other UDFs that are out there - they are prototypical versions of UDFs that are now built-in to Teradata.)  If you don't mind installing a C UDF (and have permission), this might be the easiest way to do it, but be sure to read the instructions on that page, especially the part about handling UNICODE.  And ignore/drop the line: "#define MAX_STRING_LENGTH 256"!!!

Enthusiast

Re: How to manipulate VARCHAR(32000) without getting Failed [9134 : HY000] Result exceeds the output

Thanks casting as CLOB was successful.

I don't always have access to a server to run grep.