otranslate function handling string size limit

Analytics
Highlighted
Enthusiast

otranslate function handling string size limit

Hello,

 

I am using function 'otranslate' to count the number of commas in a string. The string has 600+ characters, when I applied OTRANSLATE(mystring, ',','') on it, I found that the result returned is not complete. It seems that the OTRANSLATE only functions on the first 400 + characters, which is wierd.

 

I found that there are similar questions in forum but no clear anwers yet. Can anyone throw a light?

 

Lorraine


Accepted Solutions
Ambassador

Re: otranslate function handling string size limit

OTranslate returns a VarChar(8000), if TYPE(OTRANSLATE(mystring, ',','')) returns something else there must be another UDF with the same name.

You can check for existing UDFs using

SELECT * FROM dbc.FunctionsV
WHERE FunctionName = 'oTranslate'

The builtin is stored in td_sysfnlib.

1 ACCEPTED SOLUTION
8 REPLIES 8
Enthusiast

Re: otranslate function handling string size limit

Though I found that I can use function 'REGEXP_REPLACE' to achieve the same goal, but I'm still curious of the string size limit for 'Otranslate'.

Ambassador

Re: otranslate function handling string size limit

OTranslate returns a VarChar(8000), if TYPE(OTRANSLATE(mystring, ',','')) returns something else there must be another UDF with the same name.

You can check for existing UDFs using

SELECT * FROM dbc.FunctionsV
WHERE FunctionName = 'oTranslate'

The builtin is stored in td_sysfnlib.

Enthusiast

Re: otranslate function handling string size limit

Thanks Dieter! I ran your code and there are indeed two functions names 'otranslate'. When I specify the database name and use TD_SYSFNLIB.OTRANSLATE(item_list, ',',''), the result returned is correct :)

Teradata Employee

Re: otranslate function handling string size limit

Diether, please ;)

 I am trying to call an update to volatile table from stored procedure. 

 the query looks like 

 When I call the procedure I still get a 3707 error. Aapostrophes are doubled.

  Is there any known OTRANSLATE issue with stored procedures _? 

  If you have any hints ... please let me know

 

 thanks in advance

 

michal koubik

 

 

 SyntaxEditor Code Snippet

SyntaxEditor Code Snippet
CALL DBC.SYSEXECSQL('



CREATE SET VOLATILE TABLE tmp_vcap_st4 ,FALLBACK ,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     LOG
     (
      subscr_id INTEGER,
      asset_id INTEGER,
      party_name VARCHAR(1504) CHARACTER SET UNICODE NOT CASESPECIFIC,
      indiv_nm_gvn_nm VARCHAR(250) CHARACTER SET UNICODE NOT CASESPECIFIC,
      party_bday DATE FORMAT ''YYYY/MM/DD'',
      age INTEGER,
      p_age VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC,
      g VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,
      p_g VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( subscr_id ,asset_id )
ON COMMIT PRESERVE ROWS;



');  


CALL DBC.SYSEXECSQL(' UPDATE tmp_vcap_st4 FROM ( SELECT inh.subscr_id AS x_subscr_id, inh.asset_id AS x_asset_id ,fn.g AS x_p_g /*--,inh.p_g*/ FROM tmp_vcap_st4 AS inh INNER JOIN ( SELECT first_name_uni, first_name_c, g FROM db_crm.FIRST_NAMES QUALIFY ROW_NUMBER() OVER (PARTITION BY first_name_uni ORDER BY CAST(CNT AS NUMBER) DESC) = 1 ) AS fn ON (CAST(LOWER(OTRANSLATE(NVL(inh.indiv_nm_gvn_nm, ''000''),''á'',''a'')) AS VARCHAR(255)) = LOWER(fn.first_name_uni)) /*--WHERE inh.p_g IS NOT NULL*/ ) AS x SET p_g = x.x_p_g WHERE subscr_id = x.x_subscr_id AND asset_id = x.x_asset_id /*--AND p_g IS NULL -- komentuju a checkni s milikem*/ ;


 

 

Ambassador

Re: otranslate function handling string size limit

Which error exactly? 3707 is a generic syntax error.

 

And why do you use Dynamic SQL when you don't  have any parameters?

Teradata Employee

Re: otranslate function handling string size limit

Ghanském for reply !!
I use dynamic query because I create many volatiles and fear off failures of the proc in one session ...

If I run the update with otranslate alone it runs ok.
When put in procedure, which I am able to compile, the call fails.




Ambassador

Re: otranslate function handling string size limit


I use dynamic query because I create many volatiles and fear off failures of the proc in one session ...

I don't understand what you mean, what's the difference between creating multiple VTs directly in SPL code vs. using Dynamic SQL?

 

the call fails

What's the exact error mesage?

Teradata Employee

Re: otranslate function handling string size limit

Hi Deither, thanks for reply again.

I am using dynamic SQL because I use lot of the volatiles in late phases of the procedure (updates from, inserts to etc).

I would not be able to compile the proc if the volatiles were not created. So that is the reason.

I have cca 150 steps in the procedure (like building a house ... create, update, insert, create from, insert, update ... etc).

I do not have deep knowledge in writing the SP :( so probably I am going the wrong path.

 

Anyway - I solved the original trouble using doubled apostrophes from different keyboard in different environment.... I do not know what happend but it works for me now.

 

Regards