UDF : Data Type does not match a Defined Type name.

Teradata Debugger
Enthusiast

UDF : Data Type does not match a Defined Type name.

I have a series of dates that are in VARCHAR data types which are in possible formats of  'YYY-MM-DDThh:mm' or 'YYY-MM-DD'.

I would like a UDF to convert the dates to timestamps.

REPLACE FUNCTION dataload_35.MKDT (i VARCHAR)
   RETURNS TIMESTAMP(0)
   LANGUAGE SQL
   CONTAINS SQL
   DETERMINISTIC
   RETURNS NULL ON NULL INPUT
   SQL SECURITY DEFINER
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN (CASE when INSTR(i,'T')>0 then CAST(otranslate(trim(i)||':00','T',' ') as timestamp) else CAST(trim(i)||' 00:00:00' as timestamp) end);

However when I run a test I get an error  - what am i doing wrong? (I used as reference : http://community.teradata.com/t5/General/convert-13-digit-epoch-time-to-date/m-p/58133#comment-14817...)

  select 
    '2016-02-24T16:00' as tst
   ,mkdt(tst) as mydate
   ,'2016-02-24' as tst2
   ,mkdt(tst2) as mydate2;
   ;

Executed as Single statement.  Failed [3706 : 42000] Syntax error: Data Type "tst" does not match a Defined Type name. 
Elapsed time = 00:00:00.007 
 
STATEMENT 1: Select Statement failed. 

 

 

 


Accepted Solutions
Junior Contributor

Re: UDF : Data Type does not match a Defined Type name.

Strange indeed.

If it works in a Select it should work in a UDF, too.

Does it fail when you explain it?

 

Try to fully qualify the name

dataload_35.MKD(tst) as mydate

maybe there's another UDF with the same name (but different parameters) in your default database or syslib...

 

 

1 ACCEPTED SOLUTION
9 REPLIES
Junior Contributor

Re: UDF : Data Type does not match a Defined Type name.

REPLACE FUNCTION dataload_35.MKDT (i VARCHAR)

VARCHAR without length defaults to VARCHAR(1).

Did you work with SQL Server before (where it defaults to VARCHAR(30))?

Enthusiast

Re: UDF : Data Type does not match a Defined Type name.

Thanks Deiter for the quick reply.

No I have only ever used Teradata.

 

However I  added VARCHAR(100) to the  input  ie

REPLACE FUNCTION dataload_35.MKDT (i VARCHAR(100))

 but i still get the same error.

 

Junior Contributor

Re: UDF : Data Type does not match a Defined Type name.

Works for me as-is.

What's your Teradata version?

 

Your calculation can be simplified as Teradata doesn't care about the 'T' and just in case there's some bad data you should switch to TryCast (supported since 15.10):

 

TryCast(Trim(i) || CASE WHEN Instr(i,'T')>0 THEN ':00' ELSE ' 00:00:00' END AS TIMESTAMP(0))

 

Enthusiast

Re: UDF : Data Type does not match a Defined Type name.

Very strange.

I tried the code as you mentoned and i still get the same error.

I tested the code you suggested in a normal select string and that works fine (of course!)... so I am at a loss as to what could be wrong with the UDF.

 

VERSION16.20.21.01
RELEASE16.20.21.01
LANGUAGE SUPPORT MODEStandard
Junior Contributor

Re: UDF : Data Type does not match a Defined Type name.

Strange indeed.

If it works in a Select it should work in a UDF, too.

Does it fail when you explain it?

 

Try to fully qualify the name

dataload_35.MKD(tst) as mydate

maybe there's another UDF with the same name (but different parameters) in your default database or syslib...

 

 

Enthusiast

Re: UDF : Data Type does not match a Defined Type name.

Ok, fully qualifying the name of the UDF works!!

Thank you for the help in solving my issue!

Junior Contributor

Re: UDF : Data Type does not match a Defined Type name.

So you actually got another UDF with the same name :-)

 

To find where it's located either use

Select * from dbc.FunctionsV where FunctionName = 'MKDT'

or

SHOW select 
    '2016-02-24T16:00' as tst
   ,mkdt(tst) as mydate

 

Enthusiast

Re: UDF : Data Type does not match a Defined Type name.

Thanks for that code for UDF name checking.

FYI

I ran it and it did not find any other UDF called MKDT.

So there was no clash of names apparently. It just seems that you do (or at least I did) have to fully qualify the UDF name.

 

 

Junior Contributor

Re: UDF : Data Type does not match a Defined Type name.

Those errors can be misleading, it's a generic error when the parser finds an unknown function, I should know that :-)

 

You have to qualify when your default database is not "dataload_35".