Is there a way to use optional parameters in query?

[davide-malagoli]

Hi,

I'm trying to use the JDBC driver to connect a BI Solution we work with (TIBCO JasperSoft).
Some reports have optional parameters, and when not set these parameters are set to null.

A proper way to handle this scenario in RDBMS is creating a query like this:

SELECT
FROM

WHERE ((<optional_param IS NULL) OR ( = <optional_param>))

So, if the parameter is not set, it will not count as a filter, otherwise it will be used against a field.

I found there's no way to achieve this with the driver, because

SELECT *
FROM
WHERE NULL IS NULL

would give me parse error.

Is there any other way to handle optional parameters?

Thank you

[dai-chen]

Hi @davide-malagoli , thanks for reporting the issue! I'm not familiar with JasperSoft. I wonder is the WHERE NULL IS NULL generated from JasperSoft? Do you have any control on this?

[dai-chen]

Hi @davide-malagoli This is already fixed in new query engine. However, the new engine is disabled by default for now. You can enable it by following the instruction here: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-engine-new-enabled

[davide-malagoli]

@dai-chen Thank you, is there already any documentation of the supported features by the new sql engine?

[dai-chen]

@dai-chen Thank you, is there already any documentation of the supported features by the new sql engine?

Hi @davide-malagoli , yes, please see https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/index.rst. the docs under Language Structure and Limitations section are new added. SQL functions doc is partially rewritten. Since old and new engine will work together till we finish the migration, the old doc is still relevant. As more features migrated and added to new engine, more docs will be updated. Thanks!