Does Select Exists clause work for MSSQL in Expose...
# exposed
h
Does Select Exists clause work for MSSQL in Exposed? Mssql does not support using EXISTS() directly in select clause, and it must be wrapped in case statement (SO-post) . However, it seems that Exposed's case().When(..) does not support the
Exists
class returned from the
exists()
function. Example use case: I have Person table and PersonReview table with fk to Person table. I want to select all people and include a bit/bool value if the individual persons have a review or not.
c
Hi @Hakon Grotte Using version 0.45.0 and MSSQL, this works on our end:
Copy code
// query based on the SO suggested workaround
val existsOp = exists(Person.select { Person.name like "A%" })
val caseCondition = Case()
    .When(existsOp, intLiteral(1))
    .Else(intLiteral(0))
Person.slice(caseCondition).selectAll().toList()

// generates SQL
// SELECT CASE WHEN EXISTS (SELECT * FROM Person WHERE Person."name" LIKE 'A%') THEN 1 ELSE 0 END FROM Person
h
Thank you for your reply! 🙌 I will check out your query when I get back to my project
👍 1