CASE and Nested IF..THEN Statement Syntax

When creating a derivation, how is the: 1. CASE statement to be set up. 2. A nested IF..THEN set up. In each case, the documentation is not clear. Product Version: DecisionStream 6.5 Build: 86.39 Platform: NT

Resolving The Problem

1. To set up a CASE construct, do the following:
i) Create a function as follows:
* In the 'Functions' area of the 'Library', insert a function. Give it a name
(for example, function1)
* Click the Interface tab. Specify the attribute that will be passed as an argument to the function. Also specify the data type of that argument that will be passed to the function (for example, ProductCode - INTEGER).
* Click the Variables tab and enter the name of the variable to which the resulting value of the calculation will be assigned (for example, Prod).
* Enter the CASE statement as follows:

case (ProductCode) of
begin
1: $prod := 'prod1' ;
2: $prod := 'prod2';
default: $prod := 'defprod';
end
return $prod;

ii) In the Transformation Model, insert a derivation attribute. Assign its data type based on what will be returned by the function set up in step (i). Click the 'Calculation' tab. Right-click the calculation window area and select the User Defined function created in the previous step. Within the parentheses, specify the name of the Transformation model attribute that will be passed to the function.

2. Nested IF. THENs

CASE statements can be simulated using nested IF statements. For example, in the preceding example, it could be written:

IF (CountryCode = 1) THEN $ConvRate := 6.55957;
ELSE IF (CountryCode = 2) THEN $ConvRate := 1.95583;
ELSE IF (CountryCode = 5) THEN $ConvRate := 13.7603;
ELSE IF (CountryCode = 6) THEN $ConvRate := 1936.27;
ELSE IF (CountryCode = 7) THEN $ConvRate := 2.20371;
ELSE IF (CountryCode = 17) THEN $ConvRate := 40.3399;
ELSE IF (CountryCode = 19) THEN $ConvRate := 166.386;
ELSE IF (CountryCode = 22) THEN $ConvRate := 5.94573;
ELSE
BEGIN
$QrySpec := Concat('SELECT Rate FROM ConversionRate\
WHERE CountryCode = ', ToChar(CountryCode));
$ConvRate := Lookup('Sales', $QrySpec );
END
RETURN (Quantity*UnitPrice)/$ConvRate;

Using IF statements this way requires evaluation of each test expression until the correct branch is identified. This can be less efficient than a CASE statement, which requires the evaluation of only one expression. However, nested IF statements must be used where one expression cannot determine which branch to take. For example,

IF (VendorID = 12) THEN $ConvRate := 5.99723;
ELSE IF (CountryCode = 1) THEN $ConvRate := 6.55957;

The setup for this will be similar to the CASE (Set up function, then derivation that references the function).