Using AS values within the same query

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 2005 Forums

Transact-SQL (2005)

Using AS values within the same query

Author

Topic

sturner333
Starting Member

22 Posts

Posted - 2009-01-13 : 11:08:15

Is this possible. What would the syntax look like?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 11:22:45

No, you can't.
However if you use derived tables, you can.

select b, b * b from (
SELECT col1 / col2 AS b From table1
) AS d

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 11:23:48

yup. just use like


SELECT Fields..
FROM
(
Your current query with AS
)t
WHERE AS value ....

sturner333
Starting Member

22 Posts

Posted - 2009-01-13 : 11:37:24

Thanks for the response.
I understand derived tables to some degree. The following is my procedure and I would like to use exp24 in the select statement:

ALTER PROCEDURE dbo.allParagraphs3
AS
SELECT  sheet2.id,sheet2.year,EmployeeDerivedTable.note,
        CASE 
	  WHEN EmployeeDerivedTable.note IS NOT NULL THEN 'Bob' 
	  else 'good'
        END AS exp24
from .........

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 11:41:03

You already are using Exp24...
Or do you mean you want to use Exp24 in the WHERE clause too?

E 12°55'05.63"
N 56°04'39.26"

sturner333
Starting Member

22 Posts

Posted - 2009-01-13 : 11:45:17

Use exp24 in the select statement, something like this;

ALTER PROCEDURE dbo.allParagraphs3
AS
SELECT  sheet2.id,sheet2.year,EmployeeDerivedTable.note,
        CASE 
	  WHEN EmployeeDerivedTable.note IS NOT NULL THEN 'Bob' 
	  else 'good'
        END AS exp24,
        CASE 
	  WHEN exp24 = 'Bob' THEN 'Joe'
	  else 'bad'
        END AS exp33

from .........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 11:47:26

quote:
Originally posted by sturner333

Thanks for the response.
I understand derived tables to some degree. The following is my procedure and I would like to use exp24 in the select statement:

ALTER PROCEDURE dbo.allParagraphs3
AS
SELECT sheet2.id,sheet2.year,EmployeeDerivedTable.note,
CASE
WHEN EmployeeDerivedTable.note IS NOT NULL THEN 'Bob'
else 'good'
END AS exp24
from .........


ALTER PROCEDURE dbo.allParagraphs3
AS
SELECT otherfields...,
exp24... AS Newexpr
FROM
(
SELECT  sheet2.id,sheet2.year,EmployeeDerivedTable.note,
        CASE 
	  WHEN EmployeeDerivedTable.note IS NOT NULL THEN 'Bob' 
	  else 'good'
        END AS exp24
from .........
)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources