Question about Nulls in SELECT statement

Hi
Can someone please help me. I have 2 code examples that (i thought)
should produce the same results. The field in question (intNumber)
does allow Nulls. However, the results from Code 1 include the Null
record but the results from Code 2 do not.
- Table Data -
intNumber
4
5
NULL
- Code 1 -
SET ANSI_NULLS OFF
DECLARE @.intVar int
SELECT @.intVar = 4
SELECT * FROM tblTest WHERE (intNumber <> @.intVar)
- Results 1 -
5
NULL
- Code 2 -
SET ANSI_NULLS OFF
SELECT * FROM tblTest WHERE (intNumber <> 4)
- Results 2 -
5
Many thanks.
JuliaYes, the results are different. I strongly recommend that if possible
you avoid the ANSI_NULLS OFF setting. ANSI_NULLS OFF is a legacy
feature and most people recognize ANSI_NULLS ON as the standard to be
adopted for new code since SQL Server version 7.0.
If you must use ANSI_NULLS OFF then you'll have to cope with its
peculiarities (yes, even more peculiar than ANSI NULLs!). NULLs are
treated differently in constants, columns and variables.
David Portas
SQL Server MVP
--|||Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4. I
s
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this tabl
e
but can be found else where.
Many thanks.
Julia.|||To return all NULL rows:
WHERE COALESCE(intNumber,0) <> 4
WHERE intNumber IS NULL OR intNumber <> 4
To return no NULL rows:
WHERE COALESCE(intNumber,4) <> 4
WHERE intNumber IS NOT NULL AND intNumber <> 4
(What is an intNumber, anyway? Kind of a funny name for a piece of data.)
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
> Hi David
> Thanks for your reply and I take on board what you are saying about
> ANSI_NULLS.
> So now I have a different question really – I have a table that allows
> Nulls
> in certain columns. How to I find these records in a select statement,
> i.e.
> I want the Null records also when I say all records where intNumber <> 4.
> Is
> the ‘best practice’ solution to also include ‘or intNumber is
> null’? Or
> should I not be using Nulls at all? The reason is that my table is
> actually
> representing data that can be inherited from another source – I used
> null
> fields to recognize that this particular bit of data is not set in this
> table
> but can be found else where.
> Many thanks.
> Julia.
>|||That's the standard way, so your query would be:
select
*
from
MyTable
where
intNumber <> 4
or intNumber is null
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4.
Is
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this
table
but can be found else where.
Many thanks.
Julia.

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...
>
>