Post reply

If Statement in Computed Column

  • April 4, 2021 at 2:59 am

    #3865180

    Greetings,

    Thank You for taking the time to read and hopefully answer my question.  You will soon see how new I am to SQL Server.

    I am rebuilding in SQL Server a DB that I built in MS Access.  Several of the columns in one of the MS Access tables are calculated using IIF statements.  My question is, how can I use an IIF or IF statement in the formula for a computed column in SQL Server? Below please find the IIF statement that I used in MS Access.  When I tried to use it in SQL Server, i received an error message.

    IIf([temp_1]<=9,"Aqua",IIf([temp_1]>=10 And [temp_1]<=19,"Burnt Orange",IIf([temp_1]>=20 And [temp_1]<=29,"Beige",IIf([temp_1]>=30 And [temp_1]<=39,"Forest Green",IIf([temp_1]>=40 And [temp_1]<=49,"LTRed",IIf([temp_1]>=50 And [temp_1]<=59,"Maroon",IIf([temp_1]>=60 And [temp_1]<=69,"Amber",IIf([temp_1]>=70 And [temp_1]<=79,"LTYellow",IIf([temp_1]>=80 And [temp_1]<=89,"Dark Red",IIf([temp_1]>=90 And [temp_1]<=99,"Tan",""))))))))))

  • April 4, 2021 at 3:25 am

    #3865181

    Use CASE instead.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 4, 2021 at 4:01 am

    #3865189

    Thank You. Works great.

  • April 4, 2021 at 4:09 am

    #3865192

    Awesome.  Thanks for the feedback!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply