SQLTeam.com | Weblogs | Forums

OVER clause in CASE statement

#1

Hi. I need to create a SQL statement using OVER(partition by..) in a CASE statement, as follows. Can't get the syntax correct though..

select (sum(case when dim1 = x and dim2 = y OVER (partition by key1, key2) then measure1 end) )
from ...

Error: Incorrect syntax near the keyword 'OVER'.

Any help appreciated!

#2

A CASE clause returns a single result value. It does not include key words or object names.

For example, you'd need something more like this:

OVER(PARTITION BY CASE WHEN dim1 = x AND dim2 = y THEN key1 ELSE measure1 END, CASE WHEN dim1 = x AND dim2 = y THEN key2 ELSE NULL END))

#3

Hi

One other option is

Select sum(abc) over()
(Select case statement as abc )

Hope it helps