What value do you want returned by default?
I frequently have to code something like this:
select case
when (coalesce (cola,0)) = 0.00
or (coalesce (colb,0)) = 0.00
then 0.00 /* or null, or any thing else that makes sense in
the context */
else colb/cola
end as the_calced_value
In this instance the coalesce will return a 0 if the cola is null otherwise
it'll return the actual value. If this is in turn equal to 0 then 0.00 is
returned, otherwise the divide takes place.
There is no such thing as 'emtpy', unless you're talking about a character
type field. Even then there is a difference between a 0 length varchar and a
null value.
--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.
[quoted text, click to view] "r" <r@r.com> wrote in message news:e76BWoHQFHA.904@tk2msftngp13.phx.gbl...
> Sigh.
>
> I am creating a View, and in it I need to calculate the values in two
> columns as another column. Before I run the calculation, however, I need
> to
> make sure one of the values is not 0, null, or empty. I'm having trouble
> figuring out how to do it, though. The field I'm testing is "money" type.
>
> I've tried IsNull(), but later learned that this is meant to change a Null
> to something else. Then tried IsNumeric() expecting a 0 if not numeric,
> and
> a 1 if it is:
>
> WHEN IsNumeric(aipdn.VIEW_016_ALLMONTHS.Y2004) = 0 then 0
>
> When I run the code to create the View I get the following error message
> (which is definitely referring to the above line of code):
>
> "Implicit conversion from data type money to varchar is not allowed. Use
> the
> CONVERT function to run this query."
>
> I'm not trying to CONVERT or change the value of the Y2004 field - I
> merely
> want to make sure there is a value in there, and that it is greater than
> 0.
>
> What is the correct way to do this?
>
>
