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

[Former Member]

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.

[eralper_yilmaz · Accepted solution]

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;

[Former Member · Accepted solution]

Thanks for your detailed explanation. This is very helpful.