Archives of the TeradataForum

Message Posted: Sun, 21 Feb 2016 @ 13:22:35 GMT

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

Hi Forum,

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.'.

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?

Any inputs on this is highly appreciated.

Thanks

Roopalini

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