Aliases in View.. How ? - 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

Aliases in View.. How ?

Author

Topic

FriendOfGhost
Starting Member

9 Posts

Posted - 2011-09-04 : 19:32:29

dear friends,

here's my problem. I have a huge view with multiple case statements that makes some calculations. I'll simplify the problem below. Lets say I need to use one column's name, which is actually a subquery, in case statement.

SELECT col1, col2, (select name from customers where customers.ID=table1.customerID) AS CalculatedColumn,
case
when CalculatedColumn ='ABC' then 'A'
when CalculatedColumn ='DEF' then 'B'
when CalculatedColumn ='GHI' then 'C'
end AS resultColumn
FROM table1

I just need to know, somehow, can I do that ? My reason is, as long as I use subquery itself, my code becomes huge, almost 500 lines in query editor and its very hard to read. Yes it works right now but I'm afraid of changing it. Also as you know, when you save the view, remark lines, tabs, spaces, everything goes away and it becomes impossible to read.

also another question is about performance. As I use subquery (select statement) in every case block, I believe in my view this subquery is executed, lets say, 10 times in every case block (if I have 10 case blocks). Is that right ? If so, if I can use aliases in case blocks, I can make my query run faster.Is that right ?

What do you suggest my friends ?

thanks...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-04 : 20:42:25

treat your subquery as a derived table (in your FROM clause) rather than a calculated column. ie:

select c.name
from table1 t1
join customers c on c.id = t1.customerid

OR

select case when ...
from table1 t1
join (select <some complex stuff> from customers [group by something]) c on c.id = t1.customerid

Be One with the Optimizer
TG

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-09-04 : 21:20:45

Or you can use a common table expression (CTE):

;with myCte (col1, col2, col3)
as (select {expr}, {expr}, {expr} From customers ...)
select case when col1 = somevalue then othervalue ...
from myCte m
join table1 t1 on t1.customeris = m.id
where ...

Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-04 : 21:26:57

or even this?


SELECT col1, col2,c.name  AS CalculatedColumn,
case
when c.name='ABC' then 'A'
when c.name='DEF' then 'B'
when c.name='GHI' then 'C'
end AS resultColumn
FROM table1 t
cross apply (select name from customers where customers.ID=table1.customerID)c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FriendOfGhost
Starting Member

9 Posts

Posted - 2011-09-05 : 04:50:34

Thank you. That was very helpful...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-05 : 05:04:54

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources