Menu
Good afternoon,
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
903
In the Nested CASE expression, the outer statement executes until satisfying a condition. Once the condition is met, the inner CASE expression executes and returns a single result. If no condition is met in the outer statement, CASE expression returns the value in the ELSE statement.
The SQL CASE Expression The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
A subquery in the ELSE clause works the same way as a subquery in the THEN clause. We use a passthru predicate to evaluate the subquery conditionally. Similarly, a CASE expression with multiple WHEN clauses with subqueries in each THEN clause also works the same way.
The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.
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.
50
When field is null or ' ' case.When field is null or field = ' '
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
