question

aRookieBIdev avatar image
aRookieBIdev asked

Calculated field on calculated field

hi , In my select query i am calculating fields a and b can i also use field c based on the calculated fields a and b. example select case when col1 = 0 then 45 else null end as fielda , case when col1 = 1 then 44 else null end as fieldb, fielda + fieldb as fieldc from table
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
Yes you can but you have to use a subquery OR CTE etc. For e.g. SELECT *, fielda + fieldb as fieldc FROM ( select case when col1 = 0 then 45 else null end as fielda , case when col1 = 1 then 44 else null end as fieldb, from table ) tbl A similar use of CTE would give you better readability.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
I know it is just an example, but please note that you the columns may have NULL values because of the CASE statement used. Hence, the calculated column also may have NULL values as a result.
2 Likes 2 ·
aRookieBIdev avatar image aRookieBIdev commented ·
thanks , but i have around 50 million records , will CTE affect the preformance ?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Not sure what you mean? But If you are comparing CTE with Subquery in this case, then both should be identical performance wise IMHO. ;WITH CTE AS ( select case when col1 = 0 then 45 else null end as fielda , case when col1 = 1 then 44 else null end as fieldb from table ) SELECT *, fielda + fieldb as fieldc FROM CTE
0 Likes 0 ·
aRookieBIdev avatar image aRookieBIdev commented ·
thank you
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You could also repeat the calculation: select case when col1 = 0 then 45 else null end as fielda , case when col1 = 1 then 44 else null end as fieldb , (case when col1 = 0 then 45 else null end) + (case when col1 = 1 then 44 else null end) as fieldc from table
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image aRookieBIdev commented ·
Thanks Kev
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.