Teradata

Subtle differences in Teradata and SQL Server, Part 2

Posted by Jens Vestergaard on in Programming 0 Comment

Implementing Business logic in views is something seen quite often. Actually a view in my experience often contains some sort of data enrichment, -enhancement or -extension. So I am surely not the first one to come across the thought of adding some nifty tricks to a view. Well, tonight it seems I got too creative.

Imagine you have a table T1 with a column C1 that contains the definition of a certain type. And then we have another table T2 with a column C2 that cotains values of thses types. Now, since human interaction has been involved in populating the values in T2, there is a good change the values are missspelled, renamed or otherwise differ from the original true value. In my case there was a guaranteed match on the first six (6) characters, the rest could differ. So, I though I’d join the two tables on the columns, using only the first six characters as join condition.

The Query:

SELECT * FROM T1 JOIN T2 ON SUBSTRING(T1.C1 from 1, 6) = SUBSTRING(T2.C2 from 1, 20)

Works pretty well, until you try to create a view containing that type of join. Then you’ll get a syntax error.
Apparently the Teradata engine is parsing the statement in different ways depending on the context of use.

Needles to say, this Works in SQL Server, and is documented as well (see last note): http://technet.microsoft.com/en-us/library/aa213233(v=SQL.80).aspx

Subtle differences in Teradata and SQL Server

Posted by Jens Vestergaard on in Programming 2 Comments

Imagine the hair pulling experience, when you port knowledge from one RDBMS to another, and it just doesn’t work. I had one such experience today, when I had to generate a certain filter for at Query. I wanted to combine the year and month value, to filter on the unique month key which consisted of the concatenation of year and month (YYYYMM).

In SQL Server, you could do something like this:

SELECT
    CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4))+
    IIF(DATEPART(MONTH,GETDATE())< 10
    ,‘0’+CAST(DATEPART(MONTH,GETDATE())AS VARCHAR(2))
,CAST(DATEPART(MONTH,GETDATE())AS VARCHAR(2))
)

The result of the above query, would be ‘201402’. But, in Teradata, that just results in 2016 (for the current month of February), even when doing the explicit cast mind you.

Turns out, that CONCAT(…) is your friend – Otherwise you just end up with a number, even when explicitly casting to VARCHAR(x)

SELECT
CONCAT(
CAST( EXTRACT( YEARFROMCURRENT_DATE ) AS VARCHAR(4) ),
CASE
WHEN EXTRACT( MONTHFROMCURRENT_DATE ) < 10THEN
CONCAT( '0' , CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) ) )
ELSE
CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) )
END
)

 

How to do a String REPLACE in Teradata

Posted by Jens Vestergaard on in Programming 0 Comment

String Replace does not exist but can be accomplished with the following procedure:

INSTALL REQUIREMENTS:–

1) GRANT ALL ON “%TDUser%” TO “%TDUser%” WITH GRANT OPTION

2) C++ compiler, if running the TD Demo

How to replace “old” with “new” so that  ‘Hello, old world’ becomes ‘Hello, new world’

1) Logon to teradata using the %TDUser% login—

2) Create custom procedure

REPLACE PROCEDURE SysDBA.StringReplace ( IN TargetString VARCHAR(30000) — 33998 , IN SearchString VARCHAR(255) , IN ReplaceString VARCHAR(255) , OUT OutString VARCHAR(30000) )

BEGIN DECLARE sOutString VARCHAR(30000);
DECLARE sTmpSearchStr VARCHAR(30000);
DECLARE iTargetStrLen INTEGER;
DECLARE iSearchStrLen INTEGER;
DECLARE iSearchIndex INTEGER;

SET sOutString = ”;
SET iTargetStrLen = CHAR_LENGTH( TargetString );
SET iSearchStrLen = CHAR_LENGTH( SearchString );

IF (iSearchStrLen <= 0) THEN SET OutString = TargetString;
ELSEIF (iTargetStrLen <= 0) THEN SET OutString = ”;
ELSEIF (iSearchStrLen > iTargetStrLen) THEN SET OutString = TargetString;
ELSE SET sOutString = ”;

SET sTmpSearchStr = TargetString;
SET iSearchIndex = POSITION( SearchString IN sTmpSearchStr );

WHILE iSearchIndex <> 0 DO SET sOutString = sOutString || SUBSTR( sTmpSearchStr , 1 , iSearchIndex – 1 ) || ReplaceString ;

SET sTmpSearchStr = SUBSTR( sTmpSearchStr , iSearchIndex + iSearchStrLen , CHAR_LENGTH(sTmpSearchStr) – (iSearchIndex + iSearchStrLen) + 1 );
SET iSearchIndex = POSITION( SearchString IN sTmpSearchStr );

END WHILE;

SET sOutString = sOutString || SUBSTR ( sTmpSearchStr , 1 , CHAR_LENGTH(TRIM(TRAILING FROM sTmpSearchStr)) ); SET OutString = sOutString; END IF;
END;

3) Note, this can only run in another procedure, so write another procedure that calls the replace procedure. DECLARE outStr VARCHAR(33998);– CALL testload.Replace(colum1, ‘old ‘, ”, OutString)—- and get the output in the OutString variable

Teradata based Data Source Views in Analysis Services

Posted by Jens Vestergaard on in Programming 3 Comments

Now here’s a catch. I was trying all night long to figure out why Visual Studio was hanging on me, every time I wanted to add a Teradata based relational data source. Turns out that the list of available objects gets populated with all the objects in the current server, regardles of permissions. So connecting this way to a medium sized server, may take 20 minutes, which is unusually slow IMO.

Usually I am very patient, but this one I didn’t have time to wait for. Luckily a co-worker made me wait it out and I could move on…