Error using Between - 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 using Between

Author

Topic

GeoTom
Starting Member

5 Posts

Posted - 2011-07-13 : 19:35:16

i'm trying to show a small section of a table using the script below but i keep getting the following error

Conversion failed when converting the varchar value 'TEST' to data type int.

any ideas what i'm doing wrong?
(
SELECT NAME,
ROW_NUMBER() OVER (ORDER BY NAME) AS TEST
FROM CORPSAMPLEASSAY
)

SELECT *
FROM CORPSAMPLEASSAY
WHERE 'TEST' BETWEEN 1 AND 10;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 21:07:30

remove the quote around TEST


SELECT *
FROM CORPSAMPLEASSAY
WHERE TEST BETWEEN 1 AND 10;

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

GeoTom
Starting Member

5 Posts

Posted - 2011-07-13 : 22:21:55

I've just tried that now i get a differnt error.
Invalid column name 'TEST'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 22:23:23

[code]
; WITH CTE AS
(
SELECT NAME,
ROW_NUMBER() OVER (ORDER BY NAME) AS TEST
FROM CORPSAMPLEASSAY
)
SELECT *
FROM ~~CORPSAMPLEASSAY~~ CTE
WHERE TEST BETWEEN 1 AND 10;
[/code]

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

GeoTom
Starting Member

5 Posts

Posted - 2011-07-13 : 22:27:49

I now get.

Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 22:36:04

my bad. Miss out the AS

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

GeoTom
Starting Member

5 Posts