How to use SAP Data Services Case Transform

This article is part of our comprehensive data services tutorial Learning SAP Data Services - Online Tutorial, you may want to check that tutorial first if you have not already done so.

What is 'Case Transform'?

Case transform is basically like 'select... case' in VB or 'switch... case' in C programming language. This is also similar to the "Router Transform" available in Informatica ETL tool. Whenever there are multiple filter conditions or we want to route data to multiple pipe lines based on some conditions, we use case transform.

Video Illustration on how to use Case Transform

We will use the EMP table (having employee names etc. along with their corresponding departments) to demonstrate the use of case transform. If you are not following this tutorial series from the beginning and do not know how the EMP table look like, just remember that, among other things, EMP table has employee name (column name ENAME), employee number (column name EMPNO) and department no (DEPTNO).

Now suppose, we want to generate 3 different flatfiles based on whether an employee belongs to department 10, 20 or 30. To do this, we will use a 'Case Transform' in the data flow and reroute the data based on the department numbers.

Options available under case transform

As you have seen in the video above, there are 3 specific options that are available inside case transform. Let's understand what are those

Produce Default Output

Case transform allows you to create your own labels (or groups) and define the condition or criteria, passing which data will flow into that group. However, it may also happen that incoming data does not match with any of the conditions in the label. Let's say, we created labels for department 10, 20 and 30. But the input record is associated with department 40. What will then happen to this record? By default, this record will flow into a default group. The label of this group is also 'default'.

However, if you are sure that the incoming data will not flow into any other groups apart from what you have designed or even if it flows, you do not need the data for the default group, you can turn-off the check box that says "Produce default output with label".

Row can be TRUE for one case only

Uncheck this option, if there is a possibility that one incoming row will satisfy multiple label conditions. For example, let's say the condition for the first label is deptno > 10 and second label is deptno > 20, then one incoming record with department number 30 will match both the conditions. In such cases, if you have 'Row can be TRUE for one case only' checked, the data will only go to any one of the labels.

Preserve Expression Order

As explained above, where we have one row matching more than one labels and we have ticked 'Row can be TRUE for one case only', the row will flow into any one of the satisfying labels. But to which label, the row flows depends on this condition. If we have this check box checked, the row will follow the order of the labels and accordingly flow into the first label.