Proper use of a nested Case statement in a SQL Case Statement

Good afternoo开发者_StackOverflown,

I am currently working through some postgresql code and translating it over to sql (most if it is pretty simple) but I came across a case statement with an array in it and can't figure out the proper syntax as I've never seen a case statement used in this way before.

I've made a quick example of what i'm trying to do but it's still throwing a syntax error:

Select field3, field 4, 
Case 
    When field in (1, 3, 7) then 1
    When field in (2, 4, 6) then 2
    When field in (5, 9) then 3
    When field is null or ' ' then 4
Else
Case
    When field2 = x then 1
    When field2 = y then 2
Else End
End as fieldname

Here is the original code so youc an see what i'm editing it from and to. The case statement is (so far as I can tell as I mentioned earlier I have never used case in this manner) using 2 fields to get the desired results. Take note I didn't write this originally and am only porting it from postgresql to t-sql.

CASE 
    WHEN rank IN (1,7,9) THEN  '1'
    WHEN rank IN (2,5,10) THEN '2' 
    WHEN rank IN (3,6) THEN '3'
    WHEN rank IN (4,8) THEN '4'
    WHEN tier IS NULL OR tier = '' THEN 'N/A' ELSE 
CASE WHEN tier = 'HE' THEN '3'
    WHEN tier = 'ME' THEN '2'
    WHEN tier = 'LE' THEN '1' END
END AS tier

After working in the answers below (one of them was a typo on my part) I am now getting a syntax error on the "Else End" clause.

I modified the question to state it's a question about a nested case statement and not an array thanks

---

Case
  When field in (1, 3, 7) then 1
  When field in (2, 4, 6) then 2
  When field in (5, 9) then 3
  When field is null or ' ' then Case
    When field2 = x then 1
    When field2 = y then 2
  End --inner case
  Else null
End as fieldname 

---

am I misusing the term 'array' in this example?

Yes.

---

1. You're missing a THEN for the When field is null or ' ' case.
2. That case mentioned above should be written as When field is null or field = ' '
3. You're missing an END for the inner CASE statement within the ELSE.

---

The ELSE clauses look to be redundant:

CASE 
   WHEN field IN (1, 3, 7) THEN 1
   WHEN field IN (2, 4, 6) THEN 2
   WHEN field IN (5, 9) THEN 3
   WHEN field IS NULL THEN 4
   WHEN field2 = x THEN 1
   WHEN field2 = y THEN 2
END AS fieldname