# seq_skip_nan

> The `seq_skip_nan()` sequence function may be used to omit NaN (Not-a-Number) values in SQL sequence operations. The following steps illustrate use of this function:

> 1. Create a sample table and populate it with data, e.g.:

>         CREATE TABLE QuoteFlat(symbol string, bid float, ask float);
>         INSERT INTO QuoteFlat VALUES('AAA', 1.01, 1.02);
>         INSERT INTO QuoteFlat VALUES('AAA', 'nan', 'nan');
>         INSERT INTO QuoteFlat VALUES('AAA', 1.03, 'nan');
>         INSERT INTO QuoteFlat VALUES('AAA', 'nan', 1.05);
>         INSERT INTO QuoteFlat VALUES('AAA', 1.05, 1.06);
>         CREATE TABLE Quote(symbol string PRIMARY KEY, bid sequence(float), ask sequence(float));
>         INSERT OR UPDATE INTO Quote(symbol, bid, ask) SELECT symbol, bid, ask FROM QuoteFlat;

> 2. Now it is possible to select only valid values, e.g.:

>         SELECT symbol, seq_skip_nan(bid) FROM Quote;

> 3. Presence of NaN values in sequences causes arithmetic operations and aggregate functions to produce NaN as a result:

>         SELECT seq_avg(bid) FROM Quote WHERE symbol='AAA';
>         #1
>         ------------------------------------------------------------------------------
>         nan

> However, the `seq_skip_nan()` API can be used to ignore NaN values:

>         SELECT seq_avg(seq_skip_nan(bid)) FROM Quote WHERE symbol='AAA';
>         #1
>         ------------------------------------------------------------------------------
>         1.02999997138977

> 4. Since arithmetic operations with NaN values yield NaN, it is possible to perform operations on all elements of sequences and drop invalid results afterwards. Consider a sample where an average of bid and ask is computed:

>         SELECT (bid+ask)/2 FROM Quote WHERE symbol='AAA';
>         #1
>         ------------------------------------------------------------------------------
>         {1.01499998569489, nan, nan, nan, 1.05499994754791}

> The `seq_skip_nan()` function may be used to omit the invalid results:

>         SELECT seq_skip_nan((bid+ask)/2) FROM Quote WHERE symbol='AAA';
>         #1
>         ------------------------------------------------------------------------------
>         {1.01499998569489, 1.05499994754791}

> ### A note on NaN values in Microsoft Windows

> In the Microsoft Windows environment `NULL`s are denoted as `0.0/0.0` instead of the"nan" string. This is the value passed into the SQL statement

> Note that if `0.0/0.0` is passed as a parameter in a C/C++ application, Visual Studio 2013 does not have the "nan" or "nanf" symbols defined. Therefore the following workaround can be used in the application code:

>         #ifdef _WIN32
>             #if _MSC_VER < 1900 /* Visual Studio 2013 and earlier */
>                 #include <float.h>
>                 #define isnan   _isnan
>                 const unsigned long nan[2]={0xffffffff, 0x7fffffff};
>                 #define nan(s)      (*( double* )nan)
>                 #define nanf(s)     (*( float* )nan)
>             #endif
>         #endif

> Staring from the Visual Studio 2015 the "nan" and "nanf" symbols are present.