CODEWITHSUNDEEP
sql
Nisha Nethani
Nisha Nethani

Reputation: 109

how to use an alias name for a calculated expression in SQL

I have something like this

CASE WHEN (1-(DayDiff([END_DATE],[START_DATE])*0.01)) >= 1.5 THEN 1.5 WHEN (1-(DayDiff([END_DATE],[START_DATE])*0.01)) <=0 THEN 0 ELSE (1-(DayDiff([END_DATE],[START_DATE])*0.01)) END

Can I use alias for the formula (1-(DayDiff([END_DATE],[START_DATE])*0.01)) instead of using it multiple times?

Also I want to make the value 0 when the formula returns null.

Upvotes: 0

Views: 207

Answers (1)

Ozan
Ozan

Reputation: 1074

You can give an alias for the calculated field and use that alias in your outer query. For example;

 select 
    CASE WHEN calc_field >= 1.5 THEN 1.5
    WHEN calc_field <=0 THEN 0
    ELSE calc_field 
    END result
from (
    select (1-(DayDiff([END_DATE],[START_DATE])*0.01)) calc_field
    from table
    ) K

Upvotes: 1

Related Questions