I want to take a value from a selected column to operate the next column. For example:
SELECT CASE
WHEN ID < 4 THEN ID
ELSE 10
END
AS MY_ID,
MY_ID + 5 AS EXTRA_ID
FROM FOO
That would output for IDs 1,2,3,4,5:
MY_ID EXTRA_ID
1 6
2 7
3 8
10 15
10 15
If I do MY_ID + 5 it will complain about MY_ID not existing (it's an alias, so it makes sense) and ID + 5 will read 1+5, 2+5, 3+5, 4+5, 5+5 instead of 1+5, 2+5, 3+5, 4+10, 5+10 when it goes through the ELSE. Is it even possible to do this? I'm doing it in SSRS - Report builder, and need to operate a result that might be set to a defualt value depending on the CASE clause.