Shortcut instead of using same expressions

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

Shortcut instead of using same expressions

Author

Topic

apnw7931
Starting Member

9 Posts

Posted - 2010-12-27 : 10:35:11

Can we use in the SELECT an expression that we have just calculated.
Here is an example about my interrogation :

SELECT C1 + C2 AS [addition_1],
[addition1] + C3 AS [addition_2]

FROM my_table

instead of

SELECT C1 + C2 AS [addition_1],
C1 + C2 + C3 AS [addition_2]

FROM my_table

The reason is to be able to use shortcuts in order to avoid using always the same expression.

Thank for your help.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 10:42:23

select [addition_1], [addition_2] = [addition_1]+C3
from
(
SELECT C1 + C2 AS [addition_1],C3
FROM my_table
) a

or
;with a as
(
SELECT C1 + C2 AS [addition_1],C3
FROM my_table
)
select [addition_1], [addition_2] = [addition_1]+C3
from a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

apnw7931
Starting Member

9 Posts

Posted - 2010-12-27 : 11:32:56

Isn't there any way to do it without changing the 'main' FROM clause ?!
Because i have already join many other tables in my own query.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 11:38:56

Just change the table in the from clause to a derived table

replace
my_table
with
(SELECT C1 + C2 AS [addition_1],* FROM my_table) my_table

Or you can do the same thing with the cte and use this in the from clause.

Another option is to make your whole query a derived table and add the extra calculation outside it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

apnw7931
Starting Member

9 Posts

Posted - 2010-12-28 : 06:18:27

quote:

Originally posted by apnw7931

Thank you for your suggestions.
So far, could i ask you how you would simplify that code please ?

SELECT

CASE WHEN condition1 THEN 0
     ELSE
	     CASE WHEN condition2 THEN 0
                  WHEN condition3 THEN 1					
                  WHEN condition4 THEN 1
		  ELSE 0
	     END
END AS [C1],

	
CASE WHEN condition1 THEN 0	
     ELSE
	     CASE WHEN condition 5 THEN 0
		  ELSE 
	              CASE WHEN condition2 THEN 0
                           WHEN condition3 THEN 1
                           WHEN condition4 THEN 1
                           ELSE 0
		      END
	     END
END AS [C2],

etc...

FROM my_table

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources