Skip to Content

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 25, 2017 at 01:04 PM

    The divisors should be checked for 0 values

    Following code will help you eliminate the rows from the resultset where col1 value is equal to 0

    select 
        col1, col2 
    from TBL
    where 
        col1 > 0 and 
        col2 / (CASE WHEN col1 = 0 then null else col1 end) > 3

    What is important with your sample, using sub-select statement, we all assume that rows with col1 value equal to 0 are excluded by using where clause

    col1 > 0

    So we are sure that this criteria will exclude all rows with col1=0 so we are safe to use it as divisor

    I guess the SQL engine which parses this SQL query and creates the SQL execution path applies all WHERE clause criteria on a single step.

    Unfortunately, this smart act causes this error

    But surprisingly following code does not work also and causes division by zero error

    do 
    begin
    
    tbl_tmp2 = 
    select 
        col1, col2 
    from TBL
    where 
        col1 <> 0 ;
    
    select 
        col1, col2 
    from :tbl_tmp2
    where (col2 / col1) > 3;
    
    end;
    Add comment
    10|10000 characters needed characters exceeded

  • Oct 26, 2017 at 01:30 AM

    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
    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Oct 25, 2017 at 11:30 AM

    There might the some condition when col1 is 0. You need to handle these condition specifically with the help of CASE statement or there are also other way you can do these.

    Add comment
    10|10000 characters needed characters exceeded