Alias on Where Clause - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Alias on Where Clause

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2007-11-09 : 15:24:43

Well, i'm tired with SQL cause sometimes is to slow to code or simple queries are so big.

This is a simple query that is intented to explain my question. Supossed you have:


SELECT   
CASE  variable1  
       WHEN   1   THEN   "computer"
       WHEN   2   THEN   "computer"
       WHEN   3   THEN   "mouse"
       WHEN   4   THEN   "computer"
       WHEN   5   THEN   "mouse"
       WHEN   6   THEN   "computer"
       WHEN   7   THEN   "computer"
       WHEN   8   THEN   "laptop"
       WHEN   9   THEN   "laptop"
       WHEN   10   THEN   "computer"
END type,count(*) qty
FROM table1
WHERE 
CASE  variable1  
       WHEN   1   THEN   "computer"
       WHEN   2   THEN   "computer"
       WHEN   3   THEN   "mouse"
       WHEN   4   THEN   "computer"
       WHEN   5   THEN   "mouse"
       WHEN   6   THEN   "computer"
       WHEN   7   THEN   "computer"
       WHEN   8   THEN   "laptop"
       WHEN   9   THEN   "laptop"
       WHEN   10   THEN   "computer"
END ='computer'

GROUP BY 

CASE  variable1  
       WHEN   1   THEN   "computer"
       WHEN   2   THEN   "computer"
       WHEN   3   THEN   "mouse"
       WHEN   4   THEN   "computer"
       WHEN   5   THEN   "mouse"
       WHEN   6   THEN   "computer"
       WHEN   7   THEN   "computer"
       WHEN   8   THEN   "laptop"
       WHEN   9   THEN   "laptop"
       WHEN   10   THEN   "computer"
END

Why you have to repeat all the case statement?

Is there something that i can use like the following?


WHERE type ='computer' 
GROUP BY type

I have worked with sql for years and i couldnt find any answer.
Sometimes i have to use many queries like that to develop ASP pages, and union all the sentence in a VB String is so nasty speccially when you have to use many & _

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 15:27:28

you can only use the alias in the order by
other than that you can do this
select *
from
(
your select without where
) t
where aliasedColumns

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:40:07

You don't have to repeat for the WHERE clause...

WHERE Variable1 IN (1, 2, 4, 6, 7, 10)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:44:51

And since all Types you let through is 'Computer' this will perform better

SELECT	'Computer' AS [Type],
	COUNT(*) AS Qty
FROM	Table1
WHERE	Variable1 IN (1, 2, 4, 6, 7, 10)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:47:51

What Spirit is telling you is this

SELECT		[Type],
		COUNT(*) AS Qty
FROM		(
			SELECT	CASE Variable1  
					WHEN 1 THEN 'computer'
					WHEN 2 THEN 'computer'
					WHEN 3 THEN 'mouse'
					WHEN 4 THEN 'computer'
					WHEN 5 THEN 'mouse'
					WHEN 6 THEN 'computer'
					WHEN 7 THEN 'computer'
					WHEN 8 THEN 'laptop'
					WHEN 9 THEN 'laptop'
					WHEN 10 THEN 'computer'
					ELSE 'Not handled Variable1 value'
				END AS [Type]
			FROM	Table1
		) AS y
GROUP BY	[Type]
ORDER BY	[Type]

E 12°55'05.25"
N 56°04'39.16"

rekiller
Starting Member

31 Posts

Posted - 2007-11-09 : 15:51:31

Ok, i see that my example didnt work.

I cant use select 'computer' as [type] because what i want is to make a search from ASP, and i need to pass the parameter in the where clause to return me the IDs, is like reverse of your example.

The better aproach was
select *
from
(
your select without where
) t
where aliasedColumns

I always use that, but, i ask again, Why no ones has invented some way to make work the alias in where clause?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:59:22

Because you can create an alias which is the same as a present column name.
And then the sql engine do not know which column to use.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 16:00:48

because that would require to completly rewrite the sql server parser.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 02:47:15

Direct use of alias will work only in order by clause

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources