Dremio lacking conditional functions?

Hello, I’m having trouble on using IF, CASE, and COALESCE functions on Dremio.
The following error shows up: Failure parsing the query.

Actually, these functions do not appear in functions list on “New query”.
Some help?

please can you share the SQL? and upload the query profile

SQL:
SELECT SUM(IF(DateFirstDeposit IS NOT NULL, TotalInvested, 0)) AS Deposits
FROM customer
WHERE run = ‘2020-02-16’

Error: Failure parsing the query.
I’m uploading the screen and the query profile/


399d8bae-bf11-4685-a97b-cba4d3e0eeb0.zip (5.4 KB)

the correct SQL is using CASE

please refer to SQL supported operations.

https://docs.dremio.com/sql-reference/sql-functions/conditional.html

why not use COALESCE ? do you have error using it? (if yes, upload profile please)

Yes, but independently of which function I’m using, it should return me some value if properly used.
I tried the CASE function and the COALESCE function, and the same error persists: Failure parsing the query.

All those three functions do not appear in my function list :frowning:
0600e609-ba6e-4152-a34f-16cf8e6c9c72.zip (4.8 KB)

your query is wrong

SELECT SUM(Dep) AS Deposits
FROM (SELECT CASE WHEN DateFirstDeposit IS NOT NULL TotalInvested ELSE 0 END AS Dep
      FROM customer
      WHERE run = ‘2020 - 02 - 16’) AS T

Correct:

SELECT SUM(Dep) AS Deposits
FROM (SELECT CASE WHEN DateFirstDeposit IS NOT NULL **THEN** TotalInvested ELSE 0 END AS Dep
      FROM customer
      WHERE run = ‘2020 - 02 - 16’) AS T

Missing THEN after column name in case

Yes, I missed it. Thank you dacopan.

But anyway, IF function is not available?

IF function is not ANSI SQL, only some DBMS support it as SqlServer, Mysql,
Dremio use calcite internall similar as Drill, so you can view the supported Sql in dremio documentation or in Drill documentation.