db.value contains

[sneshvadvdl]

Hello, we were trying to using scalasql on a new project and I have the following. Using the example from the tutorial below, oracle expects the where clause to be WHERE (city0.name IN (?, ?, ?)). Is there a way i can get this to work?

thanks

val query = City.select
.filter(c => db.values(Seq("Singapore", "Kuala Lumpur", "Jakarta")).contains(c.name))
.map(_.countryCode)

db.renderSql(query) ==> """
SELECT city0.countrycode AS res
FROM city city0
WHERE (city0.name IN (VALUES (?), (?), (?)))
"""

[NPCRUS]

Hi, WHERE (city0.name IN (?, ?, ?)) and WHERE (city0.name IN (VALUES (?), (?), (?))) is essentially the same and for me returns the same results using e.g. Postgres. Do you have issues with result of a query being different from your expectation or only with rendered sql?

[sneshvadvdl]

Hi and thanks for your response. It doesn't work with Oracle, I guess it is not as permissive as Postgres regarding SQL syntax.

On the other hand, if I execute db.runRaw on the rendered sql where i remove the VALUES clause, it works. So, in summary

db.runRaw[String]("SELECT group0.formid AS res FROM group group0 WHERE (group0.state IN (VALUES((?), (?), (?))))", Seq("NEW", "UPDATED", "OLD"))

I get the following error: ORA-00936: missing expression.

db.runRaw[String]("SELECT group0.formid AS res FROM group group0 WHERE (group0.state IN ((?), (?), (?)))", Seq("NEW", "UPDATED", "OLD"))

works correctly

[NPCRUS]

I think oracle dbms is not supported at the moment. And I don't know if there are any plans to do so