Question about handling Nulls

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.
ThanksRick,
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:

> Hi,
> If I am adding 3 columns of data type real in a select clause and if any o
ne
> 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 conver
t
> 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@.metc.state.mn.us> wrote in message
news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
> 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
>|||Thank You Alejandro.
"Alejandro Mesa" 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:
>|||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:

> 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 val
ue
> that makes most sense. So, for example, 'price' would always start at zer
o
> 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@.metc.state.mn.us> wrote in message
> news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
>
>|||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@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> 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:
>|||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@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> 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:
>|||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 i
t.
Ricky
"james" 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@.metc.state.mn.us> wrote in message
> news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
>
>

Question about Font size

I'm trying to set my font to any size smaller than 8. Is that possible? I am trying to use portrait orientation instead of landscape but I can't fit everything on one page using portrait orientation with 8 point font.

Thanks!

Yes, that should be possible. Did you get an error when you set it to smaller than 8pt?|||

Its not that I received an error, it's that I can't figure out how to do it. While I am constructing the report in layout view, the smallest font size available is 8. If I change the font to a 6, it reverts right back to 8. How can I set the font size in layout view to smaller than 8?

Thanks!

|||Are you designing the report in Report Designer? Which version are you using?|||

Hi aferoce,

It is working fine in SSRS 2005.Please check in which version you are working.

Question about Font size

I'm trying to set my font to any size smaller than 8. Is that possible? I am trying to use portrait orientation instead of landscape but I can't fit everything on one page using portrait orientation with 8 point font.

Thanks!

Yes, that should be possible. Did you get an error when you set it to smaller than 8pt?|||

Its not that I received an error, it's that I can't figure out how to do it. While I am constructing the report in layout view, the smallest font size available is 8. If I change the font to a 6, it reverts right back to 8. How can I set the font size in layout view to smaller than 8?

Thanks!

|||Are you designing the report in Report Designer? Which version are you using?|||

Hi aferoce,

It is working fine in SSRS 2005.Please check in which version you are working.

Question about configuring SQL Mail with POP3 account

We want our SQL Server to send email messages when job fails. We do not
use Microsoft Exchange, we instead use Lotus Notes.
I read that if we install Outlook 2002 on the machine with SQL Server
and setup a POP3 account, the only way for it to send message is to have
Outlook 2002 left open or else it will just sit in the outbox.
We don't want to have to leave outlook 2002 open so what are other
alternatives?
If we put on Outlook 2000, will that need to be left open? Any other
soultions available to us?
Would it do anything to help if we put Lotus Notes on the machine and
set it up somehow with that?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Another alternative is to just use smtp (no mapi) to send
email. You can find an xp to do this at:
http://www.sqldev.net/xp/xpsmtp.htm
You can just add a step that sends the mail as an On Failure
step in your job.
-Sue
On Mon, 06 Oct 2003 13:37:59 -0700, Colin Colin
<ccole@.ghs.guthrie.org> wrote:
>We want our SQL Server to send email messages when job fails. We do not
>use Microsoft Exchange, we instead use Lotus Notes.
>I read that if we install Outlook 2002 on the machine with SQL Server
>and setup a POP3 account, the only way for it to send message is to have
>Outlook 2002 left open or else it will just sit in the outbox.
>We don't want to have to leave outlook 2002 open so what are other
>alternatives?
>If we put on Outlook 2000, will that need to be left open? Any other
>soultions available to us?
>Would it do anything to help if we put Lotus Notes on the machine and
>set it up somehow with that?
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!