nth_value not available in rows prior to <offset>
-------------------------------------------------
Key: CORE-4405
URL: http://tracker.firebirdsql.org/browse/CORE-4405
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Alpha 2
Environment: Win7 64
Reporter: Volker Rehn
nth_value provides absolute addressing of rows in an ordered dataset, lag/lead
provide relative addressing. These functions can be used to perform simple
spreadsheet-like operations:
select row, salary,
nth_value(salary, 10) over (order by row) nth10,
nth_value(salary, 20) over (order by row) nth20,
case
when row = 15 then 'salary(row 15) + salary(row 20)'
when row = 25 then 'salary(row 25) + salary(row 10)'
else null
end formula,
case
when row = 15 then salary + nth_value(salary, 20) over (order by row)
-- when row = 15 then salary + lead(salary, 5) over (order by row)
when row = 25 then salary + nth_value(salary, 10) over (order by row)
else null
end result
from (
select row_number() over (order by e.emp_no) row, e.salary
from employee e
The result of row 25 is correct, but the result of row 15 is wrong (null),
because nth_value(salary, 20) is not available here.
There is a note in the docs:
"FIRST_VALUE, LAST_VALUE and NTH_VALUE also operate on a window frame.
Currently, Firebird al-
ways frames from the first to the current row of the partition, not to the
last. This is likely to produce strange
results for NTH_VALUE and especially LAST_VALUE"
Does that mean nth_value won't be fixed in Firebird 3.0? For workarounds, one
can use lag/lead, but that gives messy code if cell arithmetics are more
complex.
Best regards, Volker
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Start Your Social Network Today - Download eXo Platform
Build your Enterprise Intranet with eXo Platform Software
Java Based Open Source Intranet - Social, Extensible, Cloud Ready
Get Started Now And Turn Your Intranet Into A Collaboration Platform
http://p.sf.net/sfu/ExoPlatform
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
