Hi,

If I am adding 3 columns of data type real in a select clause and if any one
of those columns is a null I get a null back instead of reslult of an
addition applied to non null columns. So I used isnull(field1,0) to convert
it to zero so I get some value back instead of a null. i.e.

select isnull(filed1,0)+isnull(field2,0)+isnull(field3,0) From XYZ

This converts null values to zero and always get a result back even though
the result may be 0 if all values are null.

My question is what if I want to get a null back ONLY if all 3 fields have
value Null otherwise some number after applying addition.

Thanks

This isn't probably the answer you want, but something you should think
about anyway.

Why do you ever want NULL for numeric values? Does it ever make
mathematical sense? We made a decision long ago to NEVER allow nulls for
numeric values. We always populate/default our numeric columns to the value
that makes most sense. So, for example, 'price' would always start at zero
rather than null. You will most likely find that if you build in default
values for all your numerics problems such as the one you are posing just
simply go away.

JIM


"Rick" <ricky.arora (AT) metc (DOT) state.mn.us> wrote

Rick,

Use a CASE expression.

Example:

select case when field1 is null and field2 is null and field3 is null then
null else isnull(filed1,0)+isnull(field2,0)+isnull(field3,0) end as
col_result From XYZ


AMB

"Rick" wrote:

Thank You Alejandro.

"Alejandro Mesa" wrote:

Hi James

You are right. What you said makes sense in the retail business for example.
I am in the wastewater industry. I can't just create zeros and average them
for the runtime of an equipment say a motor pump flow. If I get a value Zero
from pump reading only then I will average it otherwise I have to deal with
nulls and nulls are not counted in aggregate functions.

Rick

"james" wrote:

Hmm, not sure I aggree, but then I'm not the expert. Let me try though

I have 3 pumps. Pump one pumps 100gpm, P2 50 gpm and P3 isn't turned on. I
would argue that the average still includes P3, and that is is pumping zero
gpm, not NUL gpm

I'd like to see your logic/formula - but then I probably wouldn't understand
it anyway ;-)

JIM

"Rick" <ricky.arora (AT) metc (DOT) state.mn.us> wrote

Rick,

I just posted my other reply, and then it occurred to me what the problem
might be. I bet you are using a de-normalized table. And your pumps are
columns in one table, rather than having a pumps table. That is why you
have nulls in your columns.
How off the mark am I?

JIM


"Rick" <ricky.arora (AT) metc (DOT) state.mn.us> wrote

James,

You assumed that if the pump if off the value is Zero. But thats not the
case all the time. Some times instruments read bogus value of say 2 gpm
(gallons per minute) but you don't want to count this value in an avergae
neither you want a zero.

So I am applying a range condition saying if the pump value is in between
certain range I don't want it in an average. But if it is a zer then count it.

Ricky

"james" wrote: