Use Case Accelerators > How to Achieve Case Functionality

How to Achieve Case Functionality

Article #: Product: Version:

Summary

The SQL CASE expression is used to get an associated value for a given input value and is defined as follows:

CASE input_expression

WHEN search_expression THEN result_expression

[ ...n ]

[ ELSE else_result_expression ]

END

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.

Resolution

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:

SELECT supplier_id,

CASE supplier_id

WHEN 10000 THEN 'Neiman Marcus'

WHEN 10001 THEN 'Macy’’s'

WHEN 10002 THEN 'J.C. Penney'

ELSE 'Kmart'

END

AS supplier_name

FROM suppliers;

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:

Attachments

55_CASE_using_IfThenElse.zip, compatible with DMExpress version 8.0.5 or higher

Additional Information

The IfThenElse conditional function can be used to implement other variations of the CASE expression in different databases, such as:

Last updated: