Selection Operation in Query Language

In query language expressions you can apply selection operations which allow you to obtain one of the possible values meeting specified conditions.

Selection operations are described by the following set of rules:

<Selection operation>
	|
	CASE  <Selection alternatives>  [ELSE <Expression>] END
		|
		<Single selection> [<Selection alternatives>]
			|
			WHEN <Logical expression> THEN <Expression>

In selection operations, you can specify an unlimited number of alternative single selections WHEN… THEN. These are processed in the query in sequence; if <Logical expression> is TRUE, selection operation processing is completed; the operation result is the value of an expression specified after the THEN word.

The value of an expression specified after the ELSE word is used as the selection operation result if the predicate was FALSE in all the previously indicated alternative single selections.

See also: Use of Expressions in the Query Language

Example:

SELECT
Catalog.Nomenclature.Name,
CASE
   WHEN Catalog.Nomenclature.Isgroup = TRUE
      THEN "It Is Group"
   WHEN Catalog.Nomenclature.ProcurementPrice> 1000
      THEN "1000 -"
   WHEN Catalog.Nomenclature.ProcurementPrice> 100
      THEN "100 – 1000"
   WHEN Catalog.Nomenclature.ProcurementPrice> 10
      THEN "10 – 100"
   WHEN Catalog.Nomenclature.ProcurementPrice> 0
      THEN "0 – 10"
ELSE
   "Undefined"
END Price

Query result:

Name	Price
Children's pants	100 – 1000
"Cowboy" shirt	1000 –
Clothes	This is group
Women's jeans	1000 –
Children's sweater	Undefined
Sanitaryware	This is group
Everyday goods	This is group
"Lilia" sink	Undefined
"Aquarium" bathroom	Undefined
"Ultra" mixer	100 – 1000
Krups food processor	1000 –
Braun grinder	Undefined
Krups electric knife	Undefined
"Ogonek" gas stove lighter	Undefined
Accounting calculator	Undefined
Kitchen accessories	This is group
Organizational accessories	This is group