# question

## 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

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

·
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.

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

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 ·
thanks , but i have around 50 million records , will CTE affect the preformance ?
0 Likes 0 ·
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 ·
thank you
0 Likes 0 ·
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

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

Thanks Kev
0 Likes 0 · 