Writing a complex case statement in Sequel?

Question

I have a fairly complex case statement that works in MySQL:

SELECT # rest of code omitted
CASE WHEN code = 'a' THEN 'x'
  WHEN code IN ('m', 'n') THEN 'y'
  WHEN class IN ('p', 'q') AND amount < 0 THEN 'z'
  ELSE NULL END AS status
FROM # rest of code omitted

However, all attempts to write this in Sequel have failed. I am using this as a template:

Sequel.case([[:c, 1], [:d, 2]], 0) # (CASE WHEN "c" THEN 1 WHEN "d" THEN 2 ELSE 0 END)

(from Jeremy Evans' Github)

My best guess would be:

dataset.select( # rest of code omitted...
[[(:code => 'a'), 'x'],
[(:code => 'b'), 'y'],
[(:class => ['p', 'q'], :amount < 0), 'z']].case(nil).as(:status))

Any ideas?

Answer 1

After having a play with this, I have concluded that although the sequel gem aims to be be "simple, flexible and powerful", its syntax gets quite convoluted when things get a bit tricky.

Here is my best attempt at your query:

DB[:testtable].select( 
  Sequel.case([
  [{code: 'a'}, 'x'],
  [{code: ['m', 'n']}, 'y'], 
  [{class: ['p', 'q'], (Sequel.expr(:amount) > 0) => true}, 'z']], 
  nil).
  as(:status)
)

This produces the following (almost correct) SQL:

SELECT (
CASE WHEN (`code` = 'a') THEN 'x' 
WHEN (`code` IN ('m', 'n')) THEN 'y' 
WHEN ((`class` IN ('p', 'q')) AND ((`amount` > 0) IS TRUE)) THEN 'z' 
ELSE NULL END) AS `status` FROM `testtable`

I could not figure out how to use the inequality operator within a case statement. Maybe you'll have more luck with that.

My suggestion is that you just write the query in SQL, it will be significantly easier to read, and more maintainable.