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
 General SQL Server Forums
 New to SQL Server Programming
 Error in syntax
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pkovarik
Starting Member

Czech Republic
6 Posts

Posted - 02/26/2014 :  17:11:39  Show Profile  Reply with Quote
Please help me to correct error. I have query

WITH TGrp
AS
(
SELECT timeon,timeoff,ROW_NUMBER() OVER (PARTITION BY id ORDER BY timeon DESC) AS rn
FROM Alarms
WHERE timeon>='2014.02.19 22:33:45.000' AND timeon<='2014.02.26 22:33:45.000'
)
Select ROW_NUMBER() OVER (ORDER BY timeon) as x, * FROM Tgrp WHERE (rn=1) and (x between 6009 and 6051)

but I've received the error messsage "Invalid column name 'x'."
If I'll remove last condition (x between 6009 and 6051) it's ok. But I need to maintain this condition for the range selection. What is the correct syntax to eliminate error message?

bitsmed
Constraint Violating Yak Guru

415 Posts

Posted - 02/26/2014 :  17:31:31  Show Profile  Reply with Quote
WITH TGrp
AS
(
SELECT timeon,timeoff,ROW_NUMBER() OVER (PARTITION BY id ORDER BY timeon DESC) AS rn
FROM Alarms
WHERE timeon>='2014.02.19 22:33:45.000' AND timeon<='2014.02.26 22:33:45.000'
)
select * from (
Select ROW_NUMBER() OVER (ORDER BY timeon) as x, * FROM Tgrp WHERE (rn=1)
) as a where x between 6009 and 6051
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/27/2014 :  02:33:22  Show Profile  Reply with Quote
quote:
Originally posted by pkovarik

Please help me to correct error. I have query

WITH TGrp
AS
(
SELECT timeon,timeoff,ROW_NUMBER() OVER (PARTITION BY id ORDER BY timeon DESC) AS rn
FROM Alarms
WHERE timeon>='2014.02.19 22:33:45.000' AND timeon<='2014.02.26 22:33:45.000'
)
Select ROW_NUMBER() OVER (ORDER BY timeon) as x, * FROM Tgrp WHERE (rn=1) and (x between 6009 and 6051)

but I've received the error messsage "Invalid column name 'x'."
If I'll remove last condition (x between 6009 and 6051) it's ok. But I need to maintain this condition for the range selection. What is the correct syntax to eliminate error message?


The reason is x is computed column at the same level so you cant use alias in where directly
make it into a derived table as per sugestion provided and then you'll be able to use this column in filter

------------------------------------------------------------------------------------------------------
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.05 seconds. Powered By: Snitz Forums 2000