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
