In this tutorial, we will learn how to use SAP data services Case Transform. Case transform provide us a means to implement "If... Then... Else" logic in data services. In the end a short video is provided with this tutorial to give you a hands-on feel in data services.
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.
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.
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
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".
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.
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.