Error in syntax - SQL Server Forums

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

Error in syntax

Author

Topic

pkovarik
Starting Member

6 Posts

Posted - 2014-02-26 : 17:11:39

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
Aged Yak Warrior

545 Posts

Posted - 2014-02-26 : 17:31:31

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 02:33:22

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources