Archives of the TeradataForum

Message Posted: Sun, 21 Feb 2016 @ 14:30:12 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Store procedure Error

From:		Dieter Noeth

Roopalini Bakthavachalam wrote:

A store procedure that worked perfectly on Teradata 15 (development server) is not working on Teradata 14 (production server). Here is the code snippet and the error.

          > IF varBracketCounter = 0 THEN
          > SET varCodeString = SUBSTR(varCodeString,0,varDec + 1) || ' As A' ||
          > CAST(varInc AS VARCHAR(10)) ||
          > TDRight(varCodeString,(CAST(LENGTH(varCodeString) AS INT)-varDec));
          > END IF;

TDRight is a UDF that works fine when called by itself, however it's throwing the below error when called inside the SP.

Missing/Invalid SQL statement'E(9267):A nondeterministic SQL expression passed as an argument to an SQL UDF cannot correspond to a parameter that appears more than once in the return statement.'.

For some (mainly older) UDFs the deterministic flag was changed in 14.10. (in early versions this was set to NOT DETERMINISTIC as default (based on Standard SQL), but never atually used).

> Here is the UDF definition for TdRight.

          > REPLACE FUNCTION SYSLIB.TdRight (InputString VARCHAR(8000), Len INT)
          >     RETURNS VARCHAR(8000)
          >     LANGUAGE SQL
          >     CONTAINS SQL
          >     DETERMINISTIC
          >     COLLATION INVOKER
          >     INLINE TYPE 1
          >     RETURN SUBSTR(TRIM(InputString),LENGTH(InputString)-(Len-1),Len);

Is the error due to having 'InputString' more than once in the RETURN statement which does not work on version 14?

It's the "Len", which is calculated using "LENGTH" in your call:

     TDRight(varCodeString,(CAST(LENGTH(varCodeString) AS INT)-varDec))

You might re-run the REPLACE FUNCTION LENGTH using DETERMINISTIC.

Of course the best solution in your case is to get rid of LENGTH using Standard SQL's CHAR_LENGTH instead.

I never understood why LENGTH is a FastPath-UDF written in C instead of a simple SQL-UDF and why you need another syntax if there's already 6 variations: CHARACTER_LENGTH/CHAR_LENGTH/CHARACTERS/CHARACTER/CHARS/CHAR

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference