How to avoid hand-written CASE statements in TimeXtender

Spring is almost here, which makes it a good time for some spring cleaning of your TimeXtender implementations. In this blog series, Senior TimeXtender Consultant Ruairidh Smith zooms in on typical things you can improve, especially for long-running implementations or implementations built by multiple developers. These are things that are usually seen as "details" that matter less than delivering new data. In this blog, he discusses how to avoid hand-written CASE statements.

Technical Debt

There is a saying: "but in this world nothing can be said to be certain, except death and taxes" (Benjamin Franklin). To these two "certainties" I would like to add a third: Technical Debt. Technical Debt can be described as the future cost of rework caused by choosing a simple or quick solution to a problem that would be better solved in another, more time-consuming way. Like entropy or a loan, Technical Debt accrues "interest", so the problem grows over time and becomes harder and harder to tackle. At some point, all those "we'll do it properly later" shortcuts conspire to bring your production environment to a spectacular halt.

Although the automation TimeXtender does for you already helps prevent a lot of Technical Debt, you will always implement some business logic yourself in SQL. By reviewing this code and keeping it as simple as possible, you keep your environment maintainable in the future. If you are new to TimeXtender, this often means adapting instincts from other tools, because TimeXtender automates certain things directly that you would otherwise solve with manual code.

How do you avoid hand-written CASE statements?

The first topic I want to touch on is one I come across very often: CASE statements in Field Transformations that determine which value should be stored in a field. In such a case, you will usually want to build a solution that follows IF-THEN-ELSE logic. In the example we use here, we look at the Class field in the AdventureWorks Product table: this field contains four values:

• NULL: unknown class
• H: high product
• M: medium product
• L: low product

A typical approach, especially for the SQL experts among us, is to write a CASE statement that picks up the description belonging to the code. You can find an example of such syntax in Figure 1. In this specific case you will probably think: "the code documents itself", but in real-life situations the expressions are often more complex. Because there is a good chance the logic will change over time, you have to maintain this code, and developers will have to read it to understand why something happens that they did not expect.

Conditional Field Transformations

In TimeXtender it is possible to automate this kind of transformation: Conditional Field Transformations. This approach lets you implement the same logic, but in a way that is easier to read and maintain. See Figure 2 for an example that implements the setup of the original CASE statement.

The big difference where you have to "think differently" is that you use Fixed Transformations for all values with conditions for all situations that are not the default (the ELSE in the IF-THEN-ELSE). You first create a Transformation for the ELSE, without a condition. Then, for each IF-THEN, you add a Fixed Transformation with a Condition that represents the IF.

As long as your CASE conditions do not overlap, the order of the transformations with conditions does not matter much (as long as you keep the ELSE at the top). If conditions do overlap, you must put the condition that "wins" at the bottom.

In my experience, the result is easier to understand for non-SQL developers and prevents the syntax variations that can occur because different developers have different coding styles. You can also drag transformations and conditions between fields, so you can quickly apply the same IF-THEN-ELSE to multiple fields.

Although this may seem trivial, it makes your business logic explicitly visible instead of hiding it in a Custom Transformation that you first have to open to read in full. I recommend implementing new logic this way and setting aside some time to walk through your existing transformations. That way you avoid a future Friday afternoon at 4:58 pm when the proverbial Technical Debt Collector pays you a visit and forces you to fix a 1,000-line CASE statement that you pieced together a year ago from three Stack Overflow posts you ran through ChatGPT.

Technical footnote: The syntax TimeXtender generates from your Conditional Transformations consists of nested CASE statements instead of a list of WHENs as in the raw SQL example. You can view the syntax by right-clicking the table > Advanced > Customize Code and clicking the Add button for the Transformation View. You will see that the order runs from bottom to top: the bottom (conditional) transformation for which the condition is true is applied.

There are some subtleties in the evaluation order of CASE statements (see also this link) and limitations on the depth of nesting. This means you can normally apply a maximum of 10 conditional transformations to a single field in TimeXtender. If you write the code by hand, you can fit in many more WHENs; however, in that case I would recommend using a lookup table to retrieve the description based on a key.