Subtle differences in Teradata and SQL Server, Part 2

Posted on 17/02/2014 by Jens Vestergaard

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

Posted in Programming | Tagged , | Leave a comment

Why it takes a DIY-guy a week to replace a faucet

Posted on 16/02/2014 by Jens Vestergaard

This is from back in 2011, but still valid I think

For just about any DIY-guy comes the time where the faucet has held back its last drop, and must be replaced. One might initially think that this would be a relatively easy task. Of the possible combinations no outcome would be disastrous, but there could of course have been reversed hot and cold Water – or no Water at all .

All this was well thought through when I took off to Bauhaus to buy new faucet . One of my final questions were ”do I have everything I need? ” to which the expert replied ” Yeah, it should be square .” Just let me reveal now, that it was far from it.

When I first tried to install the pipes without mounting the battery itself , I discovered that the supplied thread did not fit on the extension tube . Hmpf! Shop was closed, so I had to re- install the old faucet .

Status Day 1 : no change

Back to Bauhaus where an employee cocksure announced that “it can only be those that fit ” so I rolled good-natured Home, finally to install the new faucet . Sure enough, they fit, the new gizmos, amazing! But it was only a partial goal. The mixer proved to be for the kitchen, so the piece under the table, filled more space than there was room for. In connection with this discovery, which of course meant the dismantling of the existing faucet was one of the hoses that connected pipe with faucet leaking due to damaged packaging.

Status Day 2 : still the old faucet mounted, now just only with hot water which, incidentally, was the dripping .

On the third day I unfortunately has to spend 3.5 hours to a fantastic important extra – extraordinary general meeting (important neighborhood stuff), so there was no progress in the project.

Status Day 3 : same same

The first mixer was chosen partly by design and partly by long-term use in mind. Now we had to choose again, and the priority now became price and feature the highest. We would now just need a mixer that could do the job for a short period . Somewhere on the horizon there is a new bathroom waiting, if it is while we live here is not known . Anyway, it was Silvan who was able to deliver a faucet that just met price + functionality, but they were so not able to deliver that much more. It was too much for the dear service representative when I explained how it was installed . When the original tube from balofixen was extended by a snake, it was too confusing for the employee. ” I need a coupling with external thread ” I explained, then Silvan boy quite upset proclaimed that ” that’s a strange system you have. I’ve never experienced it before.” Without seeming too defeatist and indulgent, I tried to point to one of the many variants that hung on the wall, and it did indeed finally get the his bulb glow a little. Now I had the feeling of being closer to the target than before. And the Silvan employee also seemed as if he had said the right things. Tugging in the kids and other domestic obligations, meant no more progress on the project that day.

Status Day 4 : same same, now just with the proper fittings, and the right faucet .

Friday I was to have the kids on my own, but I would just try to mount the bugger while they were in the bathtub. It all went fine, right up until the cold connection should be installed . The tube on the new faucet was of inscrutable reasons, two inches shorter than the original. So pipe was missing. # % } { % } ^ ^ # ^ { % | $ | | % {

The old faucet I had not dealt with very elegantly, so I could not mount it again. I therefore had to mount the warm support for the new faucet, which was now sitting in the sink. The cold could not connect, so I mounted it on the discarded faucet and put it under the sink. You should just not turn on the old faucet, the it would pour out on the floor.

Status Day 5 : new faucet, but only with hot water.

Saturday morning I took off to Silvan to buy a longer pipe to connect the cold end . With Rosa in the Triobike  we were quickly on the road. There was good tailwind and therefore high speed. It was fatal in an S-bend at the café Ae. Here a wheel suddenly lost contact with the pavement, and I lost control of the bike. I tried to ease the impact with my leg, which was perhaps silly, but maybe the reason why nothing happened to Rosa. Luckily nothing happened to her, she looked a little glum for me when I rocked the bike back on all three wheels. But soon won her good mood again. Now we were almos there, so we hurried in Silvan bought the hose and limped home bound, very slowly.

ER couldn’t tell me anything, because of the swelling in the knee. Now, hit hard on mobility, lying with wrenches and other tools under a sink was not exactly top of the to-do list.

Status Day 6: all purchases made, now we just wait for mobility. So close to the target now :)

Sunday I could half sitting, half lying attach the hose so we could get cold water. Bingo! It worked! And you don’t notice the slight trickle when you are with your leg up high, here on the sofa in the living room.

Status Day 7 : Alles klar Herr Kommisar , well almost…

Posted in Personal | Tagged , | Leave a comment

Subtle differences in Teradata and SQL Server

Posted on 11/02/2014 by Jens Vestergaard

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
)

 

Posted in Programming | Tagged , , | 2 Comments

Selected to speak at #SQLSat275

Posted on 04/02/2014 by Jens Vestergaard

If you haven’t already heard about the concept of SQL Saturday, I suggest you rush of to www.sqlsaturday.com and dive into the numerous opportunities to attend free Microsoft SQL Server training sessions.

One such SQL Saturday is comming to Copenhagen, Denmark, on March 29th 2014 where I have been so Lucky as to get selected to speak in one of the slots. My session is going to be about Security in Analysis Services, a session I have done a couple of times before, so now it should have matured and hopefully improved :)

The Friday before the Free event, there is a Pre-Con held at the same location, where you at a bargain can attend one of the following session:

Scaling SQL Server 2014 – Glenn Berry (MVP)    
Blog: www.sqlskills.com/blogs/glenn
Twitter: @GlennAlanBerry

Level : 300

Description
SQL Server implementations can quickly evolve and become more complex, forcing DBAs and developers to think about how they can scale their solution quickly and effectively. Scaling up is relatively easy (but can be expensive), while scaling out requires significant engineering time and effort. If you suggest hardware upgrades you may be accused of simply “throwing hardware at the problem”, and if you try to scale out, you may be thwarted by a lack of development resources or 3rd party software restrictions. As your database server nears its load capacity, what can you do? This session gives you concrete, practical advice on how to deal with this situation. Starting with your present workload, configuration and hardware, we will explore how to find and alleviate bottlenecks, whether they are workload related, configuration related, or hardware related. Next, we will cover how you can decide whether you should scale up or scale out your data tier. Once that decision is made, you will learn how to scale up properly, with nearly zero down-time. If you decide to scale out, you will learn about practical, production-ready techniques such as vertical partitioning, horizontal partitioning, and data dependent routing. We will also cover how to use middle-tier caching and other application techniques to increase your overall scalability.

Create Solutions with Power BI – Marco Russo (MVP) 
Blog: sqlblog.com/blogs/marco_russo
Twitter: @marcorus

Level : 300

Description
Microsoft Power BI is a self-service business intelligence (BI) solution that provides data analysis and visualization capabilities. It contains a set of add-in that you can use in Excel (Power Query, Power Pivot, Power View and Power Map), plus a set of services integrated in Office 365 to share, collaborate, get answers and insights (such us publishing data, Power View visualization in HTML5 and Q&A) and a mobile app that improves access on any device.

In this full-day seminar, Marco Russo guides you in creating a complete solution step-by-step, using all the features of Power BI. Starting from scratch, you see how to leverage on Power Query to import and integrate data from many different sources, with a particular attention on leveraging existing data in the company, transforming data in order to improve the resulting model and sharing the results of the queries created, so that they will be easy to reuse. Then you see how to create a Power Pivot model following the best practices and using the resources available on the web in order to accelerate the creation of tables and formulas that are shared in many models. The reference data model will improve over the day, adding metadata to improve the usability of other Power BI features.

Once the data model is ready, you see how to create dashboards in Excel, leveraging pivot tables, Power View and regular Excel formatting. After publishing the result on Office 365, you see how to refresh the data on the cloud using the Data Management Gateway, including all the details about correct configuration and best practices for moving on premise data to the cloud in a secure way.

Once the model is published, more analytics are available: you see how to use Power View and Q&A, learning how to optimize the data model for a better user experience during data exploration with these tools. Q&A enables queries written in natural language, and it might require some information in the data model in order to disambiguate similar names or similar relationships.

Finally, you see how to display data on maps using both Power View and Power Map, which offers visualizations that are more advanced and enables the production of a video, useful for a high impact presentation. At the end of the day, you will be ready to start using the entire Power BI stack in your company, choosing the right feature for each requirement and applying the best practices in each step.

Understanding Execution Plans – Hugo Kornelis (MVP)   
Blog: sqlblog.com/blogs/hugo_kornelis
Twitter: @Hugo_Kornelis

Level : 300

Description
For troubleshooting long running queries, looking at the execution plan is often a good starting point. Once you know how the query is executed, you know why it’s slow and what you can do to speed it up. But what if the execution plan is just beyond your understanding? What if it uses operators you have seen before, but do not really understand? What if you look at the execution plan, but just don’t see the problem?

In this full-day workshop, you will learn everything you need to be able to read and understand any execution plan. We’ll start with an overview of execution plans as a whole, and then dive in and look at all the components, and how they fit together. This will increase your understanding on why the optimizer picks a plan, and what you can do to make it pick a better plan.

Whether you have read your share of execution plans or whether you wouldn’t know where to find them, this workshop will teach you everything you need to know about execution plans. Attend this workshop if you want to hone your tuning skills!

Throughout the day, we will have several exercises to help you get an even better understanding of the theory. In order to get the most out of the day, attendees are advised to bring a laptop, with SQL Server (any version, but if you use SQL Server Express, you will need the advanced tools as well) and the AdventureWorks sample database pre-installed.

Price: 800 DKK (early-bird price: 600 DKK, if you register before 31 January 2014)

Register for the pre-con at : https://www.eventbrite.com/e/sqlsaturday-275-denmark-pre-conference-registration-9776266075?ref=ebtn

Register for the Free event at: http://www.sqlsaturday.com/275/eventhome.aspx

But be quick as seats fill up quickly :)

Posted in Programming | Tagged , , | Leave a comment

How to do a String REPLACE in Teradata

Posted on 04/02/2014 by Jens Vestergaard

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

Posted in Programming | Tagged , | Leave a comment