Building a Case expression

A CASE expression is used to display different values depending on a set of conditions. It is a powerful expression and is widely in SQL.

Building a new Case Expression

This can be done in two places:

• in the Query Builder > New Column > Build Case clause
• in the SQL Window > Insert > Column Case statement

Amending a Case Expression

If you have a Case expression in an existing SQL statement, then you can amend this as folows:

• import the SQL into the Query Builder
• go to the Column tab > select the column expression > Mod > Build Case clause

AQT will parse the Case clause and display it in the Case dialog for you to amend.

Example - Simple Case

This example is of a simple Case expression, in which the conditions involve a single column. This is often used to translate the values of a column to a more displayable format.

SELECT tbspace,

Case datatype

When 'A' Then 'Regular'

When 'L' Then 'Long'

When 'T' Then 'Temp'

When 'U' Then 'User Temp'

Else 'Other'

End

FROM SYSCAT.TABLESPACES

Example - Complex Case

In this example the conditions are more complicated than just comparing a single column to a set of values.

SELECT EmpNo,

Case

When Salary > 100000 Then 1000

When Commision_Type = 'F' Then Salary * 0.10

Else Salary * Commision

End as Commission_Paid

FROM EMPLOYEE