Open topic with navigation
The SQL CASE expression is used to get an associated value for a given input value and is defined as follows:
WHEN search_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
It compares the input expression against one or more specified search values in the order given, and returns either:
This functionality can be achieved in DMExpress using the IfThenElse conditional function.
The following SQL statement uses the CASE expression to get the corresponding supplier name for a given supplier ID by comparing it against a list of supplier ID values:
WHEN 10000 THEN 'Neiman Marcus'
WHEN 10001 THEN 'Macy’’s'
WHEN 10002 THEN 'J.C. Penney'
The attached example demonstrates how to achieve this functionality in DMExpress.
The task is a simple Copy task that defines a named condition, DecodeSupplierId, as a nested IfThenElse function, equivalent to the SQL CASE expression above:
This named condition is then used in the target reformat to output the input Supplier Id and the corresponding Supplier Name, resulting in the following output based on the given input:
55_CASE_using_IfThenElse.zip, compatible with DMExpress version 8.0.5 or higher
The IfThenElse conditional function can be used to implement other variations of the CASE expression in different databases, such as:
Copyright © 2016 Syncsort All rights reserved.