# SQL Sequence Cross Function

> The `seq_cross()` function returns a sequence of values indicating where the input sequence crosses zero. The second argument specifies whether to indicate the position where the sequence crosses zero in the positive or negative direction: a negative value indicates that zero-crossings in the downward direction are desired; a positive value indicates that zero-crossings in the upward direction are desired; a value of 0 indicates zero-crossings in either direction are desired.

> Following is an example script demonstrating the `seq_cross()` function:

>         INSERT INTO SimpleSequence(testNumber,iVal1,iVal2)
>         VALUES(8,'{-1,0,1,-3,2}');

>         SELECT iVal1,seq_cross(iVal1,-1) AS "cross-neg",seq_cross(iVal1,1) AS "cross-pos",seq_cross(iVal1,0) AS "cross-0"
>         FROM SimpleSequence WHERE testNumber=8;

>         iVal1{}
>         cross-neg{}
>         cross-pos{}
>         cross-0{}
>         --------------------------------------------------
>         {-1, 0, 1, -3, 2}
>         {3}
>         {1, 4}
>         {1, 3, 4}

> So, in the example above the result sequences are obtained as follows:

> - `cross-neg{}`: The first three elements of iVal1 (`-1,0,1`) are increasing values, but the element 3 (`-3`) crosses zero in the negative direction. So the position 3 is copied to the result sequence.
> - `cross-pos{}`: The first two elements of iVal1 (`-1,0`) show that at element 1 (`0`) is the first zero crossing in the positive direction. So the position 1 is copied to the result sequence.The elements 3 and 4 of iVal1 (`-3,2`) show that at element 4 (`2`) is the next zero crossing in the positive direction. So the position 4 is copied to the result sequence.
> - `cross-0{}`: The result sequence when the second argument to `seq_cross()` is 0 are simply the positions of both negative and positive zero-crossings. So the positive crossing at element 1 and 4 are combined with the negative crossing at element 3 (as obtained above) are copied in ascending order (`1,3,4`) to the result sequence.

> ## 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 5