Sql Empty Values In Calculation Returning Error In Formula

[TroyPurdue]

I have a formula that is returning an Error in Formula because it is calculating all blank entries. I've tried to replace the blank with a '0' when it finds all empty values. The formula should return a 0% when the calculation finds all empty values. Here is what I have that is not working:

 

select

 

case when count(*) = 0

 

then 0

 

else count(*)

 

end as (sum(IsNull(Accident_Injured,0))+sum(IsNull(Accident_InjuriesYes,0))+sum(IsNull(Accident_Damage,0))+sum(IsNull(Accident_Flipped,0)))/(count(Accident_Injured)+count(Accident_InjuriesYes)+count(Accident_Damage)+count(Accident_Flipped))

 

from RA_Received

 

where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]' AND PreCall_SupervisorLocation='[@Location]' AND PreCall_Supervisor='[@Supervisor]' AND PreCall_AC='[@AC]'

[TroyPurdue]

Bump.

[Aether]

Hi @TroyPurdue,

Please note that you cannot use the Aggregated functions directly in the Formula or Calculated fields:

For more information, check this documentation: https://howto.caspio.com/function-reference/

~WatashiwaJin~

[JolliBeng]

Hi,

Just to update this post, after the release 19.0, the IsNull() Function in your formula might not work. Using IsNull() in Formula Fields, Calculated Values and Calculated Fields will only accept date/time values for the replacement value. 

Please see the link below for more details:

https://howto.caspio.com/release-notes/caspio-bridge-19-0/19-0-impacted-areas/

 

~JolliBeng