All Forums General
nezoic 6 posts Joined 06/12
07 Sep 2012
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? 

nezoic 6 posts Joined 06/12
07 Sep 2012

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.

 

ulrich 816 posts Joined 09/09
07 Sep 2012

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.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

nezoic 6 posts Joined 06/12
13 Sep 2012

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.

 

nezoic 6 posts Joined 06/12
13 Sep 2012

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?

ulrich 816 posts Joined 09/09
13 Sep 2012

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');

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Fred 1096 posts Joined 08/04
16 Sep 2012

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.

You must sign in to leave a comment.