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) )
Error: Incorrect syntax near the keyword 'OVER'.
Any help appreciated!
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))
One other option is
Select sum(abc) over()
(Select case statement as abc )
Hope it helps