Conditional SQL Functions

The following conditional functions take one input sequence or two (then, else) sequences, and a Boolean sequence condition. The result sequence of the same type except for seq_filter_pos() which returns a result sequence of type uint4.

The two input sequence iterator arguments must be of the same type and result is a sequence of values, or a scalar value. If the two input sequence arguments are of different lengths the operation will be performed on only the number of elements in the shorter of the two sequences.

seq_iif( cond, then, else )	Choose the element of the then sequence or the else sequence depending on the Boolean value of the element of the cond sequence. All three sequences are traversed at the same speed (if the element of the then sequence is used, then the element of else sequence is skipped and visa versa). (Please see the Ternary Operations page for further details.)
seq_if( cond, then, else )	Choose the element of the then sequence or the else sequence depending on the Boolean value of the element of the cond sequence. Unlike seq_iif(), the position in the sequence not used is not changed. (Please see the Ternary Operations page for further details.)
seq_filter( cond, input )	Choose the elements of the input sequence for which the Boolean elements of the cond sequence are true.
seq_filter_pos( cond )	Get positions of the elements that are true in sequence cond.

Example

Following is an example code snippet demonstrating the conditional functions:

     
    -- seq_iif, seq_if
            
     
    INSERT INTO SimpleSequence(testNumber,bVal1,iVal2,iVal3)
        VALUES(1,'{0,1,0,0,1}','{-1,-2,-3,-4,-5}','{1,2,3,4,5}');
     
    SELECT bVal1,iVal2,iVal3,seq_iif(bVal1,iVal2,iVal3) AS "iif" FROM SimpleSequence
    WHERE testNumber=1;
    SELECT bVal1,iVal2,iVal3,seq_if(bVal1,iVal2,iVal3) AS "if" FROM SimpleSequence
    WHERE testNumber=1;
     
    bVal1{}
    iVal2{}
    iVal3{}
    iif{}
    -------------------------------------------------------------------------
    {0, 1, 0, 0, 1}
    {-1, -2, -3, -4, -5}
    {1, 2, 3, 4, 5}
    {1, -2, 3, 4, -5}
     
    bVal1{}
    iVal2{}
    iVal3{}
    if{}
    -------------------------------------------------------------------------
    {0, 1, 0, 0, 1}
    {-1, -2, -3, -4, -5}
    {1, 2, 3, 4, 5}
    {1, -1, 2, 3, -2}
     
     
    -- seq_filter
            
     
    INSERT INTO SimpleSequence(testNumber,bVal1,iVal1)
        VALUES(1,'{0,1,0,1}','{42,-13,27,19}');
         
    SELECT bVal1, iVal1, seq_filter(bVal1,iVal1) AS "filter" FROM SimpleSequence 
    WHERE testNumber = 1;
     
    bVal1{}  
    iVal1{}   
    filter{}
    -------------------------------------------------------------------------
    {0, 1, 0, 1} 
       {42, -13, 27, 19}       
    {-13, 19}
     
     
    -- seq_filter_pos
            
     
    INSERT INTO SimpleSequence(testNumber,bVal1)
        VALUES(11,[1,0,1,0]);
     
    SELECT bVal1,seq_filter_pos(bVal1) As "filter_pos" FROM SimpleSequence 
    WHERE testNumber=11;
     
    bVal1   
    filter_pos
    -------------------------------------------------------------------------
    {1, 0, 1, 0} 
       {0, 2}