6

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?

the Tin Man
  • 150,910
  • 39
  • 198
  • 279
user1706938
  • 121
  • 7
  • 2
    Have you asked on [Sequel-talk](https://groups.google.com/forum/?fromgroups#!forum/sequel-talk)? That's where Jeremy Evans and the gurus hang out. – the Tin Man Mar 02 '13 at 03:43

1 Answers1

6

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.

dan
  • 96
  • 2
  • Thank you for your pains! I'm inclined to agree with your last statement - though I had set out to write everything in Sequel for thoroughness, nothing is quite as readable as the original. – user1706938 Mar 05 '13 at 22:24