When teaching people to use TimeXtender or evaluating implementations I usually tell people to convert custom CASE statements into Conditional Fixed transformations because these are clearer to understand and easier to maintain.
While writing an article on this topic I discovered that the code TimeXtender generates is no longer as close to a handwritten CASE statement as it used to be.
Imagine the following setup:
I have a handwritten CASE statement that populates a ProductClassDescription field based on the contents of the [Class] field (syntax in window on bottom-right). The way CASE statements work, the first result expression that returns TRUE for its boolean expression gets returned (see: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16). This means that a record containing ‘H’ in the [Class] field will return ‘High’ for the [ProductClassDescription] field.
If I create a ‘proper’ implementation using Fixed Transformation and conditions, the order of the transformations is inverted (bottom up). This means that the last Transformation ‘wins’ and the [ProductClassDescription_Clone] field contains ‘Low’.
To exactly recreate the original CASE statement I would need to invert the order of the transformations:
You do not run into this if your conditions are disjunct and remember to put the transformation without condition at the top, but you may want to set up a more complex if-then-else with overlapping conditions that makes use of the ordering of WHENs. I would then expect to define the transformations and conditions in a top-down order.
If I add another Fixed Transformation without Condition to the bottom, all earlier Transformations are ignored in the code:
Generated code:
If the order is bottom-up I would expect only the ‘Unknown’ to be applied. I know that you can use a Default transformation to implement the ELSE in an IF-THEN-ELSE construct and avoid some of this, but I think this behaviour is wrong.
I think the best solution would be:
- Use a single CASE with separate WHENs in the case of Fixed Transformations. Order them in the order defined in the UI (top-down) , allow only one Fixed Transformation without a condition and implement this as the ELSE statement. This also avoids the 10-levels of nesting issue with simple statements like this. Of course this would be a behavioural change and would require an option to be added to control this.
- Potentially use nested CASE statements for Custom Transformations as only scalar expressions use short-circuit logic, but I am not entirely sure what the reason for nested CASE statements is in the first place
