SQL Server – Using a Calculated Field in Another Calculated Field

sql serversql-server-2012subquery

I'm trying to avoid creating a temp table for just one single field, but I need to use a calculated field in a separate calculated field. For example:

,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
        ELSE 0
         END AS RateReset

,CASE WHEN ln.TIME_RATE = 1 AND RateReset = 3 THEN 'Pass' 
      ELSE 'FAIL'
        END AS OGIntRate

Best Answer

If you just need to hack this in, you can embed the first expression in the second:

,CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
        ELSE 0
         END AS RateReset

,CASE WHEN ln.TIME_RATE = 1 
     AND (CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1
     WHEN ln.INT_RATE_CODE = 'B' THEN 2
     WHEN ln.INT_RATE_CODE = 'C' THEN 3
     ELSE 0 END) = 3 THEN 'Pass' 
      ELSE 'FAIL'
        END AS OGIntRate

Although based on this very limited excerpt of the query, you should just be able to check for INT_RATE_CODE = 'C':

,CASE WHEN ln.TIME_RATE = 1 AND ln.INT_RATE_CODE = 'C' THEN 'Pass' 
      ELSE 'FAIL'
        END AS OGIntRate

If you're able to change the table definition, you could add a computed column for the first CASE expression, and then reference it directly in the other CASE expression. Like this:

ALTER TABLE YourTableName
ADD COLUMN RateReset AS CASE WHEN ln.INT_RATE_CODE = 'A' THEN 1 WHEN ln.INT_RATE_CODE = 'B' THEN 2 WHEN ln.INT_RATE_CODE = 'C' THEN 3 ELSE 0 END;

Then OGIntRate could be based off of RateReset directly like you have in your original code.

Related Question