1

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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.