Conversion and Arithmetic Errors

re: Conversion and Arithmetic Errors

HKornelis — Tue, 06 May 2008 21:21:40 GMT

Hi Craig,

Yet another excellent post!

However, just as an FYI, there is a shorter way to avoid the division by zero error:

SELECT T1.A,

1 / NULL(T1.A, 0) AS A_Reciprocal

FROM T1 JOIN T2 ON T1.A = T2.X;

Best, Hugo

Craig Freedman's SQL Server Blog — Tue, 06 May 2008 22:02:04 GMT

In this post from last week, I gave an example of a query with a conversion where the optimizer pushes

craigfr — Tue, 06 May 2008 22:17:07 GMT

Hi Hugo,

Thanks for the tip. I believe that you meant to write NULLIF (rather than just NULL):

SELECT T1.A,

1 / NULLIF(T1.A, 0) AS A_Reciprocal

FROM T1 JOIN T2 ON T1.A = T2.X

Craig

HKornelis — Wed, 07 May 2008 10:17:08 GMT

Hi Craig,

Yes, that was exactly what I meant. Thanks for catching my typo!

Best, Hugo