A native SQL query is a query statement in the form of text that can be accepted by a certain type of data source. For example: a query statement that conforms to the Oracle query syntax should be used when using an Oracle data source.

Wyn supports Case...When, only for the data sources that use Wyn's SQL standard. Example: CSV, Excel, XML, JSON, MongoDB, etc. It can be used in the following cases:

• CustomSQLTable in dataset designer.

• SQL used to create an entity in the cached model designer.

• SQLExpression in dataset designer.

• SQL expression used to create attributes in the cached model designer.

• SQL used to create datasets based on DataSource in the report designer.

• CustomSQL in data query endpoint API.

• PreQuery in Web Json DataSource editor.

Case... When evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.

• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Format 1: Simple CASE expression: The result is determined by comparing an expression to a set of simple expressions:

CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

Example: In this example, we have added a Custom SQL Table in the dataset designer using the following Case..When SQL expression

SELECT ProductName, CASE 1 WHEN ProductName = 'Chai' THEN 'Beverage' WHEN ProductName = 'Tofu' THEN 'Dairy' END as casewhen From Products

When you preview the result is as shown below.

Format 2: Searched CASE expression: The result is determined by evaluating a set of Boolean expressions:

CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

Example: In this example, we have added a Custom SQL Table in the dataset designer using the following Case..When SQL expression

SELECT ProductName, SupplierID, CASE WHEN UnitPrice <100 THEN 'Under 100' WHEN UnitPrice >=100 AND UnitPrice<200 THEN 'Under 200' ELSE 'Over 200' END FROM Products

When you preview the result is as shown below.

Limitations

• WynSQL does not support the use of subqueries in the Case...When syntax.

• WynSQL does not support the use of Case...When expression in Order By.

• WynSQL does not support the use of Case...When expression in Group By.