Windowed functions can only appear in the SELECT o

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Windowed functions can only appear in the SELECT o

Author

Topic

bacm914
Starting Member

2 Posts

Posted - 2012-05-28 : 09:46:31

I am trying to update the ID # in this table based on Type. I keep getting the error "Windowed functions can only appear in the SELECT or ORDER BY clauses." I've tried changing it based on other things I've read but I just can't get it to work. I'm sure it's something very simple. What is wrong with this code?:

UPDATE Loc
SET Custom15 = ID
,Id =CASE Type
WHEN 'FMTD' THEN '10-'
WHEN 'FM8' THEN '20-'
END + CAST(999+ROW_NUMBER( ) OVER (PARTITION BY Type ORDER BY Loc) AS VARCHAR(15))

Thanks in advance!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-28 : 10:01:00

[code]
update l
set Custom15 = ID
,Id = NEW_ID
from
(
select *, NEW_ID = CASE Type
WHEN 'FMTD' THEN '10-'
WHEN 'FM8' THEN '20-'
END + CAST(999+ROW_NUMBER( ) OVER (PARTITION BY Type ORDER BY Loc) AS VARCHAR(15))
from Loc
) l
[/code]

KH
[spoiler]Time is always against us[/spoiler]