Btukfyl

I have a problem with Firebird (embedded - version 3.0) database with cumulative aggregates. I have the following table:

+----+---------+

|ID  |Salary   +

+----+---------+

|1   |10.00    |

+----+---------+

|2   |20.00    |

+----+---------+

|3   |35.00    |

+----+---------+

|4   |10.00    |

+----+---------+

I would like to add a third column (cum_sum) that will contain a cumulative sum, i.e .:

+----+---------+---------+

|ID  +Salary   +cum_sum  |

+----+---------+---------+

|1   |10.00    |10.00    |

+----+---------+---------+

|2   |20.00    |30.00    |

+----+---------+---------+

|3   |35.00    |65.00    |

+----+---------+---------+

|4   |10.00    |75.00    |

+----+---------+---------+

When I try to use the following code:

select id, salary, sum(salary) over (order by salary) cum_sum

  from employee

  order by salary

I get a message:

Invalid token.

Dynamic SQL Error.

SQL error code = -104.

Token unknown - line 1, column 37.

Where is the problem?

You get this error if you are using Firebird 2.5 or earlier. Window functions were introduced in Firebird 3. You will need to upgrade to Firebird 3 if you want to run the query as shown in your question.

As you claim to use Firebird 3 Embedded, I suggest that you carefully check

1. 
   

   if your Firebird embedded version is actually version 3 (eg use select rdb$get_context('SYSTEM', 'ENGINE_VERSION') from rdb$database; this works for Firebird 2.1 and higher and will report an error for earlier versions).

   
   
   

   You may have loaded a different version of Firebird. This can happen if you have a different version of the embedded library earlier on the search path. This can also happen if you have a Firebird 2.5 fbembed.dll and a Firebird 3 fbclient.dll in the same location. In Firebird 3, embedded was unified with the normal client, and no longer has a separate library (it requires additional libraries, the engine plugin, instead). However most Firebird drivers will first attempt to load fbembed.dll before falling back to fbclient.dll.

   
   


2. 
   

   if you are actually connecting with Firebird embedded, and not accidentally to a Firebird server instance of a different version (eg select rdb$get_context('SYSTEM', 'NETWORK_PROTOCOL') from rdb$database will report NULL for embedded, but a value for other connection methods).

   
   
   

   This can happen if you use the wrong connection string or otherwise incorrectly configured your driver.

   
   

Otherwise, if you can't upgrade, you will need to use the more painful option of writing a stored procedure (or block) that does this for you, or resort to equally painful recursive CTE-based solutions (that have other limitations that might get in the way).