topic "IN" operator format error with MongoDB ODBC connection query in QlikView

"IN" operator format error with MongoDB ODBC connection query

prayner — Fri, 21 Apr 2023 08:30:46 GMT

I am trying to query data from MongoDB database where column_1 is equal to a list of strings. I tried testing the following code on one string, but I get an error.

[table_1]

LOAD *

SELECT
[column_1],
FROM db
WHERE column_1 IN ('value_x-1')
;

The following error occurred:

ERROR [HY000] [Qlik][MongoDBODBC] (110) Error from MongoDB Client: Badly formed input data (Error Code: 9)

The query works fine if I filter a numeric column (e.g., WHERE column_2 = 0), so clearly the way I am formatting the WHERE IN clause is wrong. Any ideas?

Re: "IN" operator format error with MongoDB ODBC connection query

BrunPierre — Fri, 21 Apr 2023 10:43:32 GMT

Hi, have you already tried this?

Where column_1 = 'value_x-1';

Alternatively, by using the where clause in the load statement.

[table_1]: LOAD * Where Match(column_1,'Value_x-1'); SELECT [column_1], FROM db;

Re: "IN" operator format error with MongoDB ODBC connection query

prayner — Fri, 21 Apr 2023 11:22:02 GMT

It worked when I used LIKE instead of IN.

There are millions of rows so putting WHERE as load statement would be too slow.

Issue now is that I don't know how to format the query so that LIKE accepts multiple values:

e.g., WHERE column_1 LIKE ('value_x-1', 'value_x-1')

Re: "IN" operator format error with MongoDB ODBC connection query

BrunPierre — Sat, 22 Apr 2023 09:41:35 GMT

I believe Qlik does not run SQL itself, It only receives the results after the query is transferred. It could probably be the db settings.

Anyways, you can try this for multiple values.

Where column_1 Like 'value_x-1' or column_1 Like 'anotherValue';

Re: "IN" operator format error with MongoDB ODBC connection query

prayner — Mon, 24 Apr 2023 15:37:34 GMT

Thanks for getting back to me. This solution didn't work either.
Error: ERROR [HY000] [Qlik][MongoDBODBC] (110) Error from MongoDB Client: Failed to send "find" command with database "insDB": Failed to read 4 bytes: socket error or timeout (Error Code: 4)

The issue with "LIKE ... OR ..." is that I am feeding new values and unpredictable number of distinct values from column_1, I would need this function to be dynamic.

The "WHERE ... IN ..." clause is ideal, but I still don't know why that isn't working. It seems likely to be related to MongoDB, so I am going to test it directly in MongoDB.