# 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 the`seq_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