The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

# How to reference a value from current row in OLAP function

 I want build a sum in a window based on an expression, in in this expression I want reference a value from current row. How can I do that? `````` create table t ( a integer not null, b integer not null, primary key (a) ); insert into t values (0, 1); insert into t values (1, 1); insert into t values (2, 1); select sum( case when a+b > t.a then 1 else 0 end case) over (order by a) from t order by a; sum(case when t.a+t.b > t.a then 1 else 0 end) over(order by t.a asc) --------------------------------------------------------------------- 1 2 3 (3 rows) Execution time: 0.078 seconds `````` I expected in the code above always 1 as result. asked 17 May '12, 16:47 mikron 91●3●4●12 accept rate: 0%

 For an idea of how to do this, see Example 14 in this whitepaper. answered 17 May '12, 17:24 Glenn Paulley 10.7k●5●68●104 accept rate: 43% I thought about mentioned approach with two windows, how it pointed in example 14, but I didn't see, how I can apply that. My trouble is to refer to a value from current result row (not current row in window) in an expression, that used in sum. In other words, I want to access a value from current result row and a value from current window row. (18 May '12, 14:03) mikron Replies hidden Currently, you can't do that within the same window function because the semantics of the function are a consequence of the definition of the window, which, if ORDER BY is not specified, is from unbounded preceding to current row. Today, you have to recompose the expression you're trying to compute and compute it using different window functions. Recently, the SQL standard committee approved extensions that appear in SQL:2011 that permit you to refer directly to a specific value, like the value of the attribute at the current row. But I'm not aware of any product that supports that new syntax yet. (18 May '12, 14:13) Glenn Paulley
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×15

question asked: 17 May '12, 16:47

question was seen: 692 times

last updated: 18 May '12, 14:13