Avoid divide by zero with the OVER clause
<p>I have view:</p>

<pre><code>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])
</code></pre>

<p>I want to avoid divide by zero, so I'm using ISNULL: <code>isnull(ba.[TSB]/sum(ba.[TSB]))</code>.</p>

<p>Now when I run query: <code>select * from AA</code> I got message:</p>

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

<p>How can I resolve the issue?</p>