select statement based on calculated column,

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

SQL Server 2000 Forums

Transact-SQL (2000)

select statement based on calculated column,

Author

Topic

swanagetown
Starting Member

19 Posts

Posted - 2005-07-26 : 10:10:35

Hi,

Just wondering if somebody could point me in the right direct with this SP:

______________
CREATE PROCEDURE dbo.getsomething
(@n int)
AS
SELECT column1, column2, (CASE WHEN column1 LIKE 0 THEN column1 ELSE column1 - column2 END) AS column3,

FROM table1
WHERE (column3 <= @n)
GO
________________

I've created column3 but the sp tells me there's no column3.

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-26 : 10:17:17

Try this

CREATE PROCEDURE dbo.getsomething
(@n int)
AS
Select * from (
SELECT column1, column2, (CASE WHEN column1 LIKE 0 THEN column1 ELSE column1 - column2 END) AS column3,
FROM table1 ) T
WHERE (column3 <= @n)
GO

Madhivanan

Failing to plan is Planning to fail

emilg
Starting Member

13 Posts

Posted - 2005-07-26 : 11:02:30

... or

SELECT column1, column2, (CASE WHEN column1 LIKE 0 THEN column1 ELSE column1 - column2 END) AS column3
FROM table1
WHERE (
(CASE WHEN column1 LIKE 0 THEN column1 ELSE column1 - column2 END)
<= @n)

you cannot reuse a column alias from the select in the where clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources