All Forums Database
Martijn 10 posts Joined 11/11
11 Mar 2015
SQL Function regarding CHAR vs VARCHAR

Hi,
 
The following SQL function works:
 
REPLACE FUNCTION dl_stg.mmtest ( a VARCHAR(1) , b INT , C INT )
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN CASE WHEN a = 'A' THEN b+c WHEN a = 'B' THEN b-c END
;

select dl_stg.mmtest('B',10,20)
;
Gives result -10
 
The following SQL function does not work:
REPLACE FUNCTION dl_stg.mmtest ( a CHAR(1) , b INT , C INT )
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN CASE WHEN a = 'A' THEN b+c WHEN a = 'B' THEN b-c END
;

select dl_stg.mmtest('B',10,20)
;
SELECT Failed.  [9881] Function 'mmtest' called with an invalid number or type of parameters
 
 
If I change the CHAR(1) to CHARACTER(1) it does not make a difference.
The only difference is the CHAR vs VARCHAR. Why doesnt the function work with CHARACTER / CHAR ? According to the documentation this should work.
 
 Thanks!

dhirajpalse 36 posts Joined 01/08
11 Mar 2015

Hi Martijn,
This is because VARCHAR accomodates the integers and CHAR doesn't.
Thanks.

Dhiraj

Fred 1096 posts Joined 08/04
11 Mar 2015

Literals are typed as VARCHAR; to pass a literal value to this function you must explicitly CAST('B' AS CHAR(1)).
As documented in SQL DDL: Detailed Topics manual, Function Name Overloading topic
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1184_112A/Create_Authorization-Details.010.137.html
If the function is defined with CHAR as the parameter type, you can only pass CHAR.
If defined with VARCHAR, you can pass CHAR or VARCHAR (and the database will implicitly cast CHAR to VARCHAR).
If defined with CLOB, you can pass CHAR, VARCHAR, or CLOB (and the database will implicitly cast CHAR or VARCHAR to CLOB).

You must sign in to leave a comment.