Case (PQL)

This logical function evaluates a list of logical expressions, returning the a result that matches the first true expression via the pairs of arguments. 

  • Returned Output: Variant
  • Library: PQL \ Granular \ Logical
  • Version: 2023.10.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

Case( <Criteria> , <Result> , OPTIONAL <Criteria N> , OPTIONAL <Result N> , OPTIONAL <Default> )

* Click on the function's arguments above for more details on the input values.

Comments
  • Criteria: A logical check to evaluate. This argument can be a simple or complex logical statement made up of columns, values, other functions, and operators.
  • Result: What will be returned if the criteria evaluates to true. The result argument can be a simple or complex statement made up of columns, other functions or values.
  • Criteria N and Result N: means you can add as many criteria-result pairs as needed to the function (from 1 to "n").
  • Default: The optional default argument represents a result should all cases be inapplicable (false).
  • For details on how to employ and use this function see the custom column overview.
Different Function types
  • This function is similar to the PQL common function of the same name. However, it operates on a column directly at the grain.
  • It is also similar to the simpler SWITCH function.
  • As a granular function, it is used in the base SQL query itself.

Example

This example checks the quantity column and resolves to different text values based on the quantity values. Notice the more complex logic for criteria #3:

Case( [transactions].[Quantity]=1, "one", [transactions].[Quantity]=2, "two", [transactions].[Quantity]>=3 and [transactions].[Quantity]<4, "three to four", "something" )

You can see the effect from this example snippet in column #6 (case), based on the Quantity column in position #1.