Dremio lacking conditional functions?

1

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?

2

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

3

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/

image
image1859×512 34.1 KB

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

4

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)

5

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)

6

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

7

Yes, I missed it. Thank you dacopan.

But anyway, IF function is not available?

8

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.

9

Even if it’s not ANSI SQL, IF function is widely used across a lot of DBMS systems. Why not just add it? It saves a lot of keystrokes and makes coding nested queries a lot more convenient.

10

@margaux.yap

Does filtered aggregates help you? It’s SQL:2003 and introduced in v19.

So the above query:

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

Can be written in Dremio as:

SELECT SUM(TotalInvested) FILTER (WHERE DateFirstDeposit IS NOT NULL) AS Deposits
FROM customer
WHERE run = '2020-02-16'
11

Not really. It doesn’t allow for nested conditional statements. This functionality is really essential for our reporting functions.