SQL Sequence Iff and If Functions

The seq_iif() and seq_if() functions return a sequence of values that represent the result of an If-Then-Else condition. When the boolean value in sequence 1 is true, an element from sequence 2 is selected; when false from sequence 3. However, the positions in sequence 2 and 3 are advanced differently. For seq_iif() the position in both is advanced to correspond with the position in sequence 1; for seq_if() the position of only the “selected” sequence is advanced.

Following is an example script demonstrating theseq_iif() and seq_if() functions:

 
    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}
         

The result sequence for seq_iif() with the dataset above is obtained as indicated in the following table:

seq1	seq2	seq3	iif
0	-1	1	1
1	-2	2	-2
0	-3	3	3
0	-4	4	4
1	-5	5	-5

For seq_if():

seq1	seq2	seq3	if
0	-1	1	1
1	-2	2	-1
0	-3	3	2
0	-4	4	3
1	-5	5	-2

Sample script

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    f 4