Question

Getting null values even though i am using coalesce

  • 12 July 2017
  • 2 replies
  • 1041 views

coalesce(pivot_index(${se_se_lawn.total_cost},8),0)

/

coalesce(pivot_index(${se_se_summary.total_registrations},8),0)


using this function and still getting null values - any idea why this is happening?


2 replies

Userlevel 4
Badge

Hi Ankit,


Because you are using coalesce() on the denominator, you risk the chance that you’ll be dividing by zero, which is not possible. Dividing by zero would throw an error in a SQL query, but since we’re using table calculations over the result set, it’s showing as null (rather than an error).


A better approach in your use case to avoid nulls in the column would be to wrap the coalesce() around the division, so you’re applying it on the result of the division:


coalesce(
pivot_index(${se_se_lawn.total_cost},8) /
pivot_index(${se_se_summary.total_registrations},8)
, 0)

Worked like a charm.

Thanks a bunch 🙂

Reply