4est 4est - 1 year ago 75
SQL Question

Avoid divide by zero with the OVER clause

I have view:

create view AA(
ba.[FYear],right(ba.[FPeriodId],2) Month,am.[L1],am.L2,ba.[SS],ba.[SM],

sum(ba.[TSB]) OVER (PARTITION BY right(ba.[FPeriodId],2),ba.[SS]) as 'X',
ba.[TSB]/sum(ba.[TSB]) OVER (PARTITION BY right(ba.[FPeriodId],2),ba.[SS]) as 'Y'

from [table1] ba left join [b1_map] am on ba.[SS1] = am.[SS1])

I want to avoid divide by zero, so I'm using ISNULL:

Now when I run query:
select * from AA
I got message:

"The function 'ISNULL' is not a valid windowing function, and cannot be used with the OVER clause."

How can I resolve the issue?

Answer Source

Use NULLIF() to avoid divide-by-zero:

(ba.[TSB] /
 NULLIF(sum(ba.[TSB]) OVER (PARTITION BY right(ba.[FPeriodId],2), ba.[SS]), 0)
) as Y

Also, I discourage you from using single quotes for column aliases. One day, you will use them when you reference the column, introducing a hard-to-debug error. Only use single quotes for string and date constants.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download