Proper use of nested Case statement in SQL Case Statement

Question

Good day,

I am currently working through some postgresql code and translating it to sql (most if it's quite simple), but I came across a case argument with an array in it and can't figure out the correct syntax like I never saw the case argument used before thus.

I made a quick example of what I'm trying to do, but it still throws 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 source code, so you will see that I am editing it with. The fact is that (as far as I can tell, as I mentioned earlier, I never used the case this way), using 2 fields to get the desired results. Please note that I did not write this initially, and I only migrate 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 now get a syntax error in the "Else End" sentence.

I changed the question to ask him a question about the nested case argument, not an array thanks

Answer 1

 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 using the term "array" incorrectly in this example?

Yes.

Answer 2

• You are missing THEN for the case When field is null or ' ' .
• This case mentioned above should be written as When field is null or field = ' '
• You are missing END for the internal CASE statement in ELSE.

Answer 3

ELSE suggestions look 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