on 10-25-2017 10:53 AM
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.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.