ISNULL does not support operand types

1

I am getting this error when trying to execute query with isnull() function against MS SQL server.
For example, In case of isnull(r.mentions ,0) mentions I am getting ISNULL does not support operand types (DOUBLE,INTEGER) In case of VARCHAR type ISNULL does not support operand types (VARCHAR,VARCHAR). I was wondering if is there any equivalent function present in Dremio or do we have any workaround for that.

2

@ahmadimt

What’s your query? Would you be able to share the profile?

How To Share A Query Profile

3

I’m getting a similar error:

" ISNULL does not support operand types (TIMESTAMP,TIMESTAMP)"

I can share the profile privately if required.

4

The function you are looking for is named COALESCE: https://docs.dremio.com/sql-reference/sql-functions/conditional.html#coalesce

5

That works as part of a SELECT e.g.

SELECT COALESCE(end_dttm, start_dttm) AS end_dttm
FROM TABLE

but it won’t work in a where clause, which is where I was using it:

SELECT refno
FROM TABLE
WHERE COALESCE(end_dttm, start_dttm) >= ‘2019-01-01’

even though the above is perfectly valid when run against SQL Server natively I get the following error when I try it via Dremio:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘>’.

As this is SQL Server 2008 and I recently learned it is not supported I didn’t bother raising it as an issue.

6

Would it be possible to share the query profile with us?

7

Sure, sent privately.

8

Thanks, it looks like the SQL generated for SQL Server is invalid. I reported the issue to our engineering team.

9

Great, thanks for that.