CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
PowerUser
PowerUser

Reputation: 11791

How to use calculated field in another field of the same query

I have a select query with a few dozen fields. "FieldA" is a Case statement about 10 rows tall. I now have to make a "FieldB" which uses the same Case statement plus a constant.

With my current knowledge of sql-server, I'd have to repeat that Case statement twice (once for FieldA and once for FieldB). To clean up my code, how can I use fieldA in the calculation of FieldB?

Ideally, my code would look something like this:

Select
    Case ...
        When ... then ...
        When ... then ...
        When ... then ...
    End                     as FieldA,
    FieldA + 1              as FieldB
From TblSource

(I know that one option is to dump the data into a temporary table, then update that temp table. But that kind of defeats the concept of 'simplifying')

Upvotes: 4

Views: 7856

Answers (2)

gnl
gnl

Reputation: 151

@Johnny_D

There's always the possibility of using the following

Select tbl.*,tbl.FieldA+1 as FieldB, tbl.FieldA+2 as FieldC from
(
Select
    Case ...
        When ... then ...
        When ... then ...
        When ... then ...
    End as FieldA
From TblSource
) as tbl

Upvotes: 3

Lamak
Lamak

Reputation: 70668

Do it like this:

;WITH YourCTE AS
(
Select
    Case ...
        When ... then ...
        When ... then ...
        When ... then ...
    End                     as FieldA
From TblSource
)
SELECT FieldA, FieldA + 1 AS FieldB, FieldA + 2 AS FieldC ....
FROM YourCTE

Upvotes: 6

Related Questions