Avoid divide by zero with the OVER clause

Question

I have view:

create view AA(
    select 
    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: isnull(ba.[TSB]/sum(ba.[TSB])).

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 1

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.