honeysql 2022-01-06 | Slack Archive

wombawomba22:01:13

How can I do a CASE statement in a SELECT column? I.e, how would I write something like SELECT CASE WHEN 1 > 2 THEN 1 ELSE 2 END foo FROM bar?

seancorfield23:01:14

@wombawomba https://cljdoc.org/d/com.github.seancorfield/honeysql/2.2.840/doc/getting-started/sql-special-syntax-#case

seancorfield23:01:14

Just remember that expressions (function call-like stuff) has to have an extra [ .. ] around it in :select to avoid confusion with aliased columns.

seancorfield23:01:02

dev=> (sql/format {:select [ [ [:case [:> 1 2] 1 :else 2] :foo ] ] :from :bar})
["SELECT CASE WHEN ? > ? THEN ? ELSE ? END AS foo FROM bar" 1 2 1 2]

wombawomba11:01:12

#Also sent to the channel

@seancorfield copy/pasting that code causes an exception on my end:

java.lang.AssertionError: Assert failed: Alias should have two parts[:case [:> 1 2] 1 :else 2]
                          (= 2 (count x))

I'm on 1.0.461 FWIW

wombawomba12:01:29

#Also sent to the channel

It does work in Honeysql 2.x, but the actual code I'm formatting contains a bunch of #honeysql.types.SqlCall, and I don't have the time to translate all of the associated code to 2.x at the moment.

seancorfield17:01:00

@wombawomba Ah, I don't remember how you do it with 1.x, sorry, and I don't think it was documented.

wombawomba17:01:17

Alright. @seancorfield can you think of any 'wrong' way to do it?

wombawomba17:01:27

I kind of need to get it working and I'm out of ideas

wombawomba17:01:08

if there's a hacky way to do it I can go with that and then just make sure to do it properly when I migrate to 2.x

seancorfield17:01:57

It's been so long since I've used 1.x -- you know you can use both side-by-side and could change the code on a per-query basis?

wombawomba17:01:07

yeah

wombawomba17:01:32

the problem here though is that other parts of the query contain a bunch of #honeysql.types.SqlCall

wombawomba17:01:50

and those give me an error when I give them to honey.sql/format

wombawomba17:01:23

perhaps there's some easyish way to translate those other parts of the query to the equivalent hsql2?

seancorfield17:01:00

You don't need that SqlCall stuff in 2.x -- calls are just syntax [:func arg1 arg2] etc.

seancorfield17:01:00

@wombawomba The 1.x tests have examples of case/when: https://github.com/seancorfield/honeysql/blob/v1/test/honeysql/core_test.cljc#L150

wombawomba18:01:54

@seancorfield thanks! (h/format {:select [[(h/call :case [:> 1 2] 1 :else 2) :foo]] :from [:bar]}) did the trick 🙂

seancorfield18:01:38

The tests are pretty much the best "documentation" for 1.x. I'm trying hard to make that not the case for 2.x which has "proper" documentation on http://cljdoc.org 🙂

👍 1

wombawomba11:01:12

replied to a thread: dev=> (sql/format {:select [ [ [:case [:> 1 2] 1 :else 2] :foo ] ] :from :bar}) ["SELECT CASE WHEN ? > ? THEN ? ELSE ? END AS foo FROM bar" 1 2 1 2]

@seancorfield copy/pasting that code causes an exception on my end:

java.lang.AssertionError: Assert failed: Alias should have two parts[:case [:> 1 2] 1 :else 2]
                          (= 2 (count x))

I'm on 1.0.461 FWIW

wombawomba12:01:29

replied to a thread: dev=> (sql/format {:select [ [ [:case [:> 1 2] 1 :else 2] :foo ] ] :from :bar}) ["SELECT CASE WHEN ? > ? THEN ? ELSE ? END AS foo FROM bar" 1 2 1 2]

2022-01-06

Channels