CODEWITHSUNDEEP
sqlsql-servercasecalculated-columns
yojoannn
yojoannn

Reputation: 626

Column alias is not recognized in case expression

I have a case expression in a computed column in a query that will average values of DATA_SCALE_1, DATA_SCALE_2, DATA_SCALE_3, DATA_SCALE_4 where all of which are of data type integer. 

CASE  
    WHEN Form_Ref = 1091 
    THEN (DATA_SCALE_1 + DATA_SCALE_2 + DATA_SCALE_3 + DATA_SCALE_4)/4
    ELSE 
        CASE WHEN DATA_SCALE_1 IS NOT NULL 
        THEN DATA_SCALE_1
        ELSE 
            OLD_FIELD_CUST_TBL.SELECT_TEXT
        END
END AS Satisfaction

Form_Ref is an alias of dbo.SU_ENTITY_TYPE.Ref. When I use Form_Ref in the condition, it always defaults to the ELSE clause. When I explicitly specify dbo.SU_ENTITY_TYPE.Ref, it performs the THEN clause but the computed average is rounded down to the nearest integer. I need it to be at most in two decimal places.

Any inputs on why the alias is not recognized? Thanks!


UPDATE:
 To limit average to two decimal places, I used
CAST(CaseExpression AS NUMERIC(x,2)) where x is the max count of digits including the 2 decimal numbers.

Upvotes: 0

Views: 800

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

First, you don't need nested case statements. This should produce the same logic:

(CASE WHEN Form_Ref = 1091 
      THEN (DATA_SCALE_1 + DATA_SCALE_2 + DATA_SCALE_3 + DATA_SCALE_4) / 4.0
      WHEN DATA_SCALE_1 IS NOT NULL 
      THEN DATA_SCALE_1
      ELSE OLD_FIELD_CUST_TBL.SELECT_TEXT
 END) AS Satisfaction

Second, column aliases cannot be used at the same level they are defined. The behavior your are describing makes it sound like there is a column called Form_Ref in the tables (in the from clause) as well as Form_Ref being the target of an alias.

Upvotes: 1

Related Questions