SQL CASE statement issue?

[RobinDiederen]

Can somebody explain why this query doesn't work when used with elasticsearch-jdbc?

SELECT CASE WHEN TRANSFERTYPE = 4 THEN 'yes' ELSE 'no' END AS BILLABLE, FROM TIMETABLE

Whilst this does work:

SELECT CASE WHEN TRANSFERTYPE = 4 THEN 1 ELSE 0 END AS BILLABLE, FROM TIMETABLE

The error:

java.io.IOException: java.sql.SQLException: Invalid column name 'yes'.

It seems that values other than integers aren't accepted. Thing is that both queries work fine when executed from any random SQL workbench tool (I use DBeaver). I've tried a number of escapings (single quotes, double quotes, escaped quotes etc.).

BILLABLE is in my index and mapped as a (non-analyzed) string.

Background info: using MSSQL 2008, elasticsearch-jdbc 2.3.3.1, jTDS driver 1.3.1, Java 8.

What am I missing? Thanks!

[jprante]

Can you show the complete syntax of the JDBC importer execution?

It looks like a syntax error because the surrounding JSON spec is also using single quote as delimiter.

[RobinDiederen]

Sure thing!

Here's my (somewhat simplified) script:

echo ' { "type" : "jdbc", "jdbc" : { "url" : "jdbc:jtds:sqlserver://ourdbserver:1433;databaseName=ourdb;instance=erpsystem", "user" : "username", "password" : "password", "sql" : "SELECT CASE WHEN TRANSFERTYPE = 4 THEN 'yes' ELSE 'no' END AS BILLABLE, FROM TIMETABLE", "elasticsearch" : { "host" : "localhost", "port" : 32769, "autodiscover" : "true" }, "index" : "myindex'" } } ' | java \ -cp "${lib}/*" \ -Dlog4j.configurationFile=${bin}/log4j2.xml \ org.xbib.tools.Runner \ org.xbib.tools.JDBCImporter

I've tried stuff like:

"sql" : "SELECT CASE WHEN TRANSFERTYPE = 4 THEN yes ELSE no END AS BILLABLE, "sql" : "SELECT CASE WHEN TRANSFERTYPE = 4 THEN \'yes\' ELSE \'no\' END AS BILLABLE, "sql" : "SELECT CASE WHEN TRANSFERTYPE = 4 THEN \"yes\" ELSE \"'no\" END AS BILLABLE, "sql" : "SELECT CASE WHEN TRANSFERTYPE = 4 THEN \"'yes'\" ELSE \"''no'\" END AS BILLABLE,

But all to no avail...

Thanks for helping me out!

[zehbauer]

Try to remove the comma after "BILLABLE" and always escape string values as yes and no in your example with "yes" . Pretty improbable as you don´t get any parse error but worth a shot.