I am getting null for some of the rows in Amazon Athena when the absolute difference is bigger than 20 percent. I am not sure I understand the logic of why. Can someone explain?

case
when gla_sqft = AreaBuilding then 0
when ((abs(AreaBuilding- gla_sqft)/gla_sqft) > 0.2) then 1 else null
end as percent_diff_logic 

CodePudding user response：

Most likely you are getting null because there are rows where (abs (AreaBuilding-gla_sqft) / gla_sqft) is less than or equal to 0.2. Performing a quick select query (SELECT ... WHERE ((abs (AreaBuilding-gla_sqft) / gla_sqft) <= 0.2)) should eliminate my assumption.

CodePudding user response：

It seems that you have standard case of integer division, i.e. 602/2100 is equal to 0. Just multiply result of abs by 1.0 or cast it to double:

when ((abs(AreaBuilding- gla_sqft) * 1.0 /gla_sqft) > 0.2) then 1 else null

or

when ((cast(abs(AreaBuilding- gla_sqft) as double)/gla_sqft) > 0.2) then 1 else null