SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Windowed functions do not support constants as ORD
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
223 Posts

Posted - 06/16/2013 :  21:12:17  Show Profile  Reply with Quote
Hi All,

I got error:

Msg 5309, Level 16, State 1, Line 3
Windowed functions do not support constants as ORDER BY clause expressions.


My code as below:-

SELECT Row_Number() Over(ORDER BY
CASE WHEN 'UserID' = 'UserID' AND 'asc' = 'asc' THEN UserID END ASC,
CASE WHEN 'UserID' = 'UserID' AND 'asc' = 'desc' THEN UserID END DESC
) as Sno,
COUNT(*) Over() TOTALROWS, UserID, EMAIL
FROM aspnet_Membership


Please advise.

Thank you.

Regards,
Micheale

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/17/2013 :  00:10:01  Show Profile  Reply with Quote
Try this:




SELECT Row_Number() Over(ORDER BY 
CASE WHEN ('UserID' = 'UserID' AND 'asc' = 'asc') THEN UserID 
	 WHEN ('UserID' = 'UserID' AND 'asc' = 'desc') THEN -1 * UserID
	 ELSE UserID END  
) as Sno, 
COUNT(*) Over() TOTALROWS, UserID, EMAIL 
FROM aspnet_Membership 

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  01:05:37  Show Profile  Reply with Quote
what do the below conditions means? look like a trivial check to me

'UserID' = 'UserID' ,'asc' = 'asc' etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000