SQL Function regarding CHAR vs VARCHAR

Database
Teradata Employee

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!

2 REPLIES
Enthusiast

Re: SQL Function regarding CHAR vs VARCHAR

Hi Martijn,

This is because VARCHAR accomodates the integers and CHAR doesn't.

Thanks.

Teradata Employee

Re: SQL Function regarding CHAR vs VARCHAR

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/Crea...

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