I' trying to query a table(detailed) which has two columns of which one is numerator and the other is denominator part of a field.
I get '0' for denominator for some rows and as it returns NULL I get an error "divide by zero".
So, I'm using a CASE statement like
Expand|Select|Wrap|Line Numbers
- CASE WHEN [denominator]= '0'
- THEN '0'
- ELSE MIN([numerator]/[denominator]) END AS'MIN([numerator]/[denominator])'
- ,CASE WHEN [denominator]= '0'
- THEN '0'
- ELSE MAX([numerator]/[denominator]) END AS'MAX([numerator]/[denominator])'
- ,CASE WHEN [denominator]= '0'
- THEN '0'
- ELSE AVG([numerator]/[denominator]) END AS'AVG([numerator]/[denominator])'
I know this way of writing code is a bit DUMB because I have about 60 more columns on which I have to do this.
So, I was wondering if a COLAESCE function can be used here as
Expand|Select|Wrap|Line Numbers
- MIN(COLASCE([numerator]/[denominator])) AS 'MIN([numerator]/[denominator])'
Thanks in advance for your time and suggestions.
-Cinnamongirl.