SAP HANA SQL Error: [304]: division by zero ...

[#1]

In SAP HANA I had the following query (simplified):

select col1, col2 from TBL
where col1 > 0 and col2/col1 > 3

This results in the error:

[304]: division by zero undefined: search table error: [6859] AttributeEngine: divide by zero

Even when I try

select * from (
  select col1, col2 from TBL
  where col1 > 0 
) where col2/col1 > 3

results in the same error.

NOTE for simplification I changed the SQL.

TBL is acually a Graphical Calculation view and there are more attributes.

But executing the inner SQL works OK

When adding the outer where condition the error occurs.

[lbreddemann]

The "check for 0" in your query silently assumes that the col1 >0 is executed before the rest of the query.

That's a false assumption for SQL - all predicates in your statement have to be true at the same time.

With HANA2 there is a comfortable way around this: the NDIV0 function.

Another workaround, in case you're on an older version that doesn't support NDIV0 is to use NULLIF in the division:

select a,b
from TBL
where b >0 
and a/NULLIF(b, 0) >3

[Former Member]

Thx for improving the answer of Eralper and even pointing out the possibility in HANA2

[eralper_yilmaz]

Hi Lars, thank you for update.

NULLIF(col1, 0)

is the short representation of

CASE WHEN col1 = 0 THEN null ELSE col1 END

[lbreddemann]

You're right, semantically both expressions lead to the same outcome.

From an implementation point of view, CASE adds an additional type-coercion to the NULL return value, but that does not have a measurable impact on runtime or memory consumption.

More important, from my point of view, is that NULLIF makes it easier to see what the intention behind that bit of code is and it's shorter to write, as well.