SQL CASE WHEN or IF ELSE IF

Question

I have multiple conditions to meet in a case I would like to Know if I can use > < instead of defining every case

In this case when the credit rating is smaller then 3 then, the word "NO TRADE" will be inserted and larger then 3, smaller then 5 would be "POOR", and so on and so on

SELECT  ClientId, 
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    CreditRating,

        CASE CreditRating

            WHEN 0 THEN 'NO TRADE'
            WHEN 1 THEN 'NO TRADE'
            WHEN 2 THEN 'NO TRADE'
            WHEN 3 THEN 'POOR'
            WHEN 4 THEN 'POOR'
            WHEN 5 THEN 'AVARAGE'
            WHEN 6 THEN 'AVARAGE'
            WHEN 7 THEN 'GOOD'
            ELSE 'PERFECT' 

            END AS RATING

    FROM dbo.client

http://stackoverflow.com/questions/5487892/sql-server-case-when-or-then-else-end-the-or-is-not-supported (for SQL Server, it may vary by SQL implementation - make sure to add the appropriate tag) — user2864740, Sep 06 '14 at 07:06

score 11 · Accepted Answer · edited Oct 07 '19 at 15:03

11

Sure it is possible.

CASE 

WHEN CreditRating <= 2  THEN 'NO TRADE'
WHEN CreditRating  <= 4 THEN 'POOR'
WHEN CreditRating  <= 6 THEN 'AVARAGE'
WHEN CreditRating  = 7 THEN 'GOOD'

ELSE 'PERFECT' 

END AS RATING

edited Oct 07 '19 at 15:03

cнŝdk

30,215
7
54
72

answered Sep 06 '14 at 07:09

Wrapper Tech

192
1
5

Sure it is possible. Case WHEN CreditRating <= 2 THEN 'NO TRADE' WHEN CreditRating <= 4 THEN 'POOR' WHEN CreditRating <= 6 THEN 'AVARAGE' WHEN CreditRating == 7 THEN 'GOOD' ELSE 'PERFECT' END AS RATING – Wrapper Tech Sep 06 '14 at 07:20
Thanks, so it looks like, if the first case is met, it will ignore all the others. that's great. thanks – Gerrit Duvenage Sep 06 '14 at 07:21
1

SQL uses `=` for equality, not `==` – a_horse_with_no_name Sep 06 '14 at 07:39

SQL CASE WHEN or IF ELSE IF

1 Answers1