A Wish List for future versions of Teradata.

General
Enthusiast

A Wish List for future versions of Teradata.

I read the forum enhancements wish list post and figured I would start one for teradata in general. I apologize, if this isn’t the place for it.

Is there a current wish list for new versions of Teradata? I've been using it for a few months now and I think it's become the most frustrating part of my job by far. I use MSSQL, ORACLE, etc with no issues but when I have to connect to our teradata data I always sigh...

Every day little things such as string to date conversions (specifically having to specify a ‘0’ in front of single digit months or days) are very annoying and seem like trivial things that everyone else in the industry solved years ago... why isn't teradata up to speed with common SQL function logic? 

6 REPLIES
Enthusiast

Re: A Wish List for future versions of Teradata.

Just ran into another from issue, when casting SMALLINT to varchar(2) why in the world does it add 5 leading spaces and force me to do a trim before concatinating the result of the cast?

Example:

new_month as smallint = 7

sel '0' || cast(new_month as varchar(2))

returns '0     7'

but triming...

sel '0' || trim(cast(new_month as varchar(2)))

returns correctly '07'

we can now use this month to cast a concatenated string to a date.

Senior Supporter

Re: A Wish List for future versions of Teradata.

Hi,

the general place for Teradata enhancement request is

http://www.teradata-partners.com/ER/submit

Where it might be interesting to discuss new requirements in the forum as well but I guess finally you need to open an ER.

I personally would not support your specific request of allowing M/D/YYYY formats to avoid "having to specify a ‘0’ in front of single digit months" but this is out of my business. This MM/DD or DD/MM is already a nightmare ;->.  Your issue can be overcome with a simple SQL UDF.

Cast as smallint to a char(2) is challenging - small int range is from -32768 to 32767. So you need 6 char to be able to place all possible values in it, and this is what you see. And again I personally prefer it this way.

Enthusiast

Re: A Wish List for future versions of Teradata.

So say you couldn't use UDFs / didn't have access to them. Do you still think that having to do the following :

case CHARACTER_LENGTH(cast(max(DAYS_EARN_MNTH_NBR) as varchar(2))) = 1 then '0' || trim(DAYS_EARN_YEAR_NBR) 

else cast(max(DAYS_EARN_MNTH_NBR) as varchar(2)) 

then cast that as a date is better than

cast(DAYS_EARN_YEAR_NBR || DAYS_EARN_MNTH_NBR || '01' as date)

or am I missing something? Is there an easier way to do this? Because without a UDF (which I dont think any other SQL language requires to convert dates in this way) its a bit more code.

Enthusiast

Re: A Wish List for future versions of Teradata.

Sorry to correct my last statement and simplify it.

Why shouldn't we be able to do

sel cast('2012' || '-' || '9' || '-' || '01' as date) without a udf?

Senior Supporter

Re: A Wish List for future versions of Teradata.

from release 13.10 on you can create SQL UDFs which hide complex code

for example 

Create FUNCTION YOUR_UDF_DB.to_date (in_year smallint, in_month byteint)
RETURNS date
LANGUAGE SQL
CONTAINS SQL
SPECIFIC Q_Pop1
CALLED ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN CASE
when in_year between 0 and 9999 and in_month between 1 and 12 then cast(((in_year-1900) * 10000) + (in_month * 100) + 1 as date)
else NULL
END;

select YOUR_UDF_DB.to_date(2012,12)

or 

replace FUNCTION YOUR_UDF_DB.m_d_yyyy_to_mm_dd_yyyy (dt_string varchar(10))
RETURNS char(10)
LANGUAGE SQL
CONTAINS SQL
SPECIFIC m_d _yyyy
CALLED ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
case
when substr (CASE when substr (dt_string,3,1) <> '-' then '0' !! dt_string else dt_string end,6,1) <> '-'
then substr (CASE when substr (dt_string,3,1) <> '-' then '0' !! dt_string else dt_string end,1,3) !! '0' !! substr(CASE when substr (dt_string,3,1) <> '-' then '0' !! dt_string else dt_string end,4)
else dt_string
end
;

select cast(YOUR_UDF_DB.m_d_yyyy_to_mm_dd_yyyy('1-22-2012') as date format'mm-dd-yyyy');
Teradata Employee

Re: A Wish List for future versions of Teradata.

No CASE required; if you know that the value will properly fit in 2 characters you can apply explicit FORMAT:

CAST(CAST(DAYS_EARN_MO_NBR AS FORMAT '99') AS CHAR(2))

As Ulrich pointed out, if you want to submit an Enhancement Request to allow dates formatted without the leading zeros, the Teradata Partners PAC website is the correct place to do that.