The new 4D v11 SQL Choose command was initially described in another Tech Tip, The CHOOSE Command. That Tech Tip described one of the more basic uses of the command. This Tech Tip explores some of its more advanced uses.

The Choose command gives 4D programmers similar power to what users of C based and Javascipt languages have enjoyed for years: The power and flexibility of the "Conditional Expression Operator", symbolically shown as "?:". This Tech Tip demonstrates that the Choose command can perform similarly to - and is actually more powerful than - the Conditional Expression Operator.

For those not familiar with Conditional Expression Operator, its general format is:

condition ? expression_1 : expression_2;

In this format "condition", is an expression, usually a relational expression, that is first evaluated whenever the conditional expression operator is encountered. If the result of the "condition" expression is TRUE (nonzero), then "expression_1" is evaluated and the result of the evaluation becomes the result of the operation. If "condition" evaluates FALSE (zero), then "expression_2" is evaluated and the result becomes the result of the operation.

The general format of the 4D v11 SQL Choose command is very similar:

$vResult := Choose(criterion; value{; value2; ...; valueN})

The Choose command returns one of the values passed in the parameters value1, value2, etc. depending on the evaluation of the criterion parameter. Where as C and Javascript's Conditional Expression Operator "condition" always evaluates to a boolean, the criterion parameter of the Choose command can evaluate to either a Boolean or Integer type:

• If criterion evaluates to a Boolean, Choose returns value1 if the Boolean equals True and value2 if the Boolean equals False. In this case, the command expects exactly three parameters: criterion, value1 and value2.


• If criterion evaluates to an Integer, Choose returns the value whose position corresponds to criterion. In this case, the command expects at least two parameters: criterion and value1.
  Note: Be careful as the numbering of the values begins with 0 so the position of value1 is 0, value2 is 1, etc.

The Choose command is more powerful in that it first evaluates the criterion to see what type of result is going to be produced. The "condition ? expression_1 : expression_2" operator is limited to treating the result of the condition as a Boolean: FALSE equaling zero, TRUE equaling any nonzero value. Whereas with the 4D v11 SQL Choose command, if 4D determines that the criterion is a Boolean, then the first value gets returned for a TRUE result. If 4D determines that the criterion evaluates to a number, then the value that is returned is based on the ordinal value of the result, with the first value being returned based on an ordinal result of zero.

A handy boolean use of the Choose command is in building a delimited string; for example:

ARRAY TEXT($MItems_aT;0) LIST TO ARRAY("MyMenuItems";$MItems_aT) $Items_T:="" $SOA:=Size of array($MItems_aT) For ($Ndx;1;$SOA)     $Items_T:=$Items_T+$MItems_aT{$Ndx}+Choose($Ndx#$SOA;";";"") End for $Ndx:=Pop up menu($Items_T)

As with "?:", where you can embed conditions within the expressions, as shown here:

condition ? expression_1 : condition ? expression_2 : expression_3;

You can also embed Choose commands within a Choose command, as shown here:

Choose(criterion; value1; Choose(criterion; value2; value3))

In the following simple examples, criterion evaluates to an integer. In this first one $Result_L is 8 after execution:

C_LONGINT($Result_L) $Result_L := Choose( 103 % 100 ; 0 ; 1 ; 2 ; Choose( 101 % 100 ; 9 ; 8 ; 7 ; 6 ) ; 4)

In the second $Result_L is 4 after execution:

$Result_L := Choose( 104 % 100 ; 0 ; 1 ; 2 ; Choose( 101 % 100 ; 9 ; 8 ; 7 ; 6 ) ; 4 )