Any functions to replace NZ in SQL Server?

[Rico]

I'm moving some queries out of an Access front end and creating views out of
them in SQL Server 2005 express. In some of the numeric fields, I use nz
quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
Is there anything equivalent to this in SQL Server? Right now I'm using
CASE WHEN ... but it seems like an awful lot of script to write just to
replace null with a zero.

Any help would be greatly appreciated.

Thanks!

[SQL]

Re: Any functions to replace NZ in SQL Server?

use coalesce or isnull

declare @v int
select coalesce(@v,0), isnull(@v,0)


Denis the SQL Menace

http://sqlservercode.blogspot.com/

[Hugo Kornelis]

Re: Any functions to replace NZ in SQL Server?

On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @ h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
[color=blue]
>I'm moving some queries out of an Access front end and creating views out of
>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
>Is there anything equivalent to this in SQL Server? Right now I'm using
>CASE WHEN ... but it seems like an awful lot of script to write just to
>replace null with a zero.
>
>Any help would be greatly appreciated.
>
>Thanks!
>[/color]

Hi Rico,

Use COALESCE:

COALESCE (arg1, arg2, arg3, arg4, ...)

returns the first non-NULL of the supplied arguments. You need at least
two arguments, but you can add as many as you like.

--
Hugo Kornelis, SQL Server MVP

[Rico]

Re: Any functions to replace NZ in SQL Server?

Thanks Guys,

I wound up finding ISNULL before I had a chance to post back. (why do I
always find the solution right after I post).

Is there an argument for using Coalesce over IsNull?

Thanks!



"Hugo Kornelis" <hugo@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:j7sf42hg4b 78p8u1v5nj283av 4kovqivur@4ax.c om...[color=blue]
> On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @ h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>[color=green]
>>I'm moving some queries out of an Access front end and creating views out
>>of
>>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is
>>null.
>>Is there anything equivalent to this in SQL Server? Right now I'm using
>>CASE WHEN ... but it seems like an awful lot of script to write just to
>>replace null with a zero.
>>
>>Any help would be greatly appreciated.
>>
>>Thanks!
>>[/color]
>
> Hi Rico,
>
> Use COALESCE:
>
> COALESCE (arg1, arg2, arg3, arg4, ...)
>
> returns the first non-NULL of the supplied arguments. You need at least
> two arguments, but you can add as many as you like.
>
> --
> Hugo Kornelis, SQL Server MVP[/color]

[Hugo Kornelis]

Re: Any functions to replace NZ in SQL Server?

On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @ h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
[color=blue]
>Thanks Guys,
>
>I wound up finding ISNULL before I had a chance to post back. (why do I
>always find the solution right after I post).
>
>Is there an argument for using Coalesce over IsNull?[/color]

Hi Rico,

Three!

1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
on SQL Server.

2. COALESCE takes more than two arguments. If you have to find the first
non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
COALESCE.

3. Data conversion weirdness. The datatype of a COALESCE is the datatype
with highest precedence of all datatypes used in the COALESCE (same as
with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
the same as the first argument. This is extremely non-standard and can
cause very nasty and hard-to-track-down bugs.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Re: Any functions to replace NZ in SQL Server?

Rico (r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:[color=blue]
> I wound up finding ISNULL before I had a chance to post back. (why do I
> always find the solution right after I post).
>
> Is there an argument for using Coalesce over IsNull?[/color]

In theory, coalesce is what you always should use, because:

1) It's ANSI-compatible.
2) coalesce can accept list of several values, whereas isnull accepts
exactly two.

Unfortunately, there are contexts were isnull() is preferable, or the
only choice. The ones I'm thinking of are:
1) In definition of indexed views you may need to use isnull to make
the view indexable.
2) I've seen reports where using coalesce resulted in a poor query plan
whereas isnull did not. I should add that that was not really a plain-
vanilla query.

So despite these excpetions, I would recommend coalesce. Even if it's
more difficult to spell.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Lyle Fairfield]

Re: Any functions to replace NZ in SQL Server?

Null is not zero. Null is not a zero length string.

I believe that nulls were not designed to be placeholders for these
values.
We should be extremely careful when we convert nulls to values. Such
conversion could lead to error. Often it is persons without strong
grounding in mathematics and logic who make these conversions,
increasing the likelihood of such error. The best practice is likely to
be the exclusion of records with nulls in the columns we are processing
and to enter values in those where a value is appropriate. There may be
some cases where it's a good idea to substitute a zls for a null value,
but none comes to my mind at this time.

IMNSHO SQL would be more rigorous if it had no IsNull(Field,Va lue) or
corresponding Coalesce function.

[Yes, I've probably posted IsNull(Field,Va lue) solutions here; that was
then; this is now.]

[Rico]

Re: Any functions to replace NZ in SQL Server?

Excellent! Thanks!


"Hugo Kornelis" <hugo@perFact.R EMOVETHIS.info. INVALID> wrote in message
news:vttf42107b tt07jbk21cvb695 3kediiarp@4ax.c om...[color=blue]
> On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @ h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>[color=green]
>>Thanks Guys,
>>
>>I wound up finding ISNULL before I had a chance to post back. (why do I
>>always find the solution right after I post).
>>
>>Is there an argument for using Coalesce over IsNull?[/color]
>
> Hi Rico,
>
> Three!
>
> 1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
> on SQL Server.
>
> 2. COALESCE takes more than two arguments. If you have to find the first
> non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
> COALESCE.
>
> 3. Data conversion weirdness. The datatype of a COALESCE is the datatype
> with highest precedence of all datatypes used in the COALESCE (same as
> with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
> the same as the first argument. This is extremely non-standard and can
> cause very nasty and hard-to-track-down bugs.
>
> --
> Hugo Kornelis, SQL Server MVP[/color]

[Madhivanan]

Re: Any functions to replace NZ in SQL Server?

Read about IsNull Vs Coalesce

http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

Madhivanan

[Hugo Kornelis]

Re: Any functions to replace NZ in SQL Server?

On 20 Apr 2006 15:57:53 -0700, Lyle Fairfield wrote:
[color=blue]
>Null is not zero. Null is not a zero length string.
>
>I believe that nulls were not designed to be placeholders for these
>values.[/color]
(snip)

Hi Lyle,

Thus far, I agree with yoour post.

(snip)[color=blue]
> There may be
>some cases where it's a good idea to substitute a zls for a null value,
>but none comes to my mind at this time.[/color]

First, you should be awarer that COALESCE and ISNULL on SQL Server, or
Nz on Access, can not just be used to replace NULL with 0 or zero length
string - you can replace them with anything you like. Common uses are
COALESCE (SomeColumn, 'n/a') in a report. Or
COALESCE (UserSpecifiedC olumn, DefaultValue) in any query or view.
[color=blue]
>
>IMNSHO SQL would be more rigorous if it had no IsNull(Field,Va lue) or
>correspondin g Coalesce function.[/color]

I disagree with this statement. As I've shown above, COALESCE and ISNULL
can be used in very useful ways. That they might also be abused by
people who fail to think their solutions through is sad, but no reason
to abolish them. That's like forbidding cars because someone might cause
an accident while drinking and driving.

Besides, since COALESCE is just a shorthand for a specific CASE
expression, removing COALESCE from the language would have no effect;
people would just use the equivalent CASE expression.

--
Hugo Kornelis, SQL Server MVP

[David Portas]

Re: Any functions to replace NZ in SQL Server?

Lyle Fairfield wrote:[color=blue]
> We should be extremely careful when we convert nulls to values. Such
> conversion could lead to error. Often it is persons without strong
> grounding in mathematics and logic who make these conversions,
> increasing the likelihood of such error.[/color]

You think so? Nulls as formulated in SQL totally defy any standard
mathematics or logic. Any system that permits the predicate (x=x) to
evaluate to anything other than true isn't likely to win many votes
from persons with a strong grounding in mathematics. It is precisely
because nulls are so counter-intuitive that they lead to so many
mistakes in SQL. However, I do agree with your basic point that if you
regularly need to convert nulls like this it may indicate weakness in
your design or requirements.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Rico]

Re: Any functions to replace NZ in SQL Server?

I probably shouldn't open my mouth in the presence of some posters, but with
regard to converting nulls being bad design; I have a bunch of reports that
show loans and payments (just to make things simple). If I have no payment
record (a null) then I have zero payments applied to the loan. By
converting these null payment records to zero payments, is this considered
in theory bad design? Or is this an exception to that rule. Is there a
definition between what would be considered bad design and what is
considered an exception?

Not trying to raise a debate really, just asking for clarification.


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:1145653630 .430902.73680@z 34g2000cwc.goog legroups.com...[color=blue]
> Lyle Fairfield wrote:[color=green]
>> We should be extremely careful when we convert nulls to values. Such
>> conversion could lead to error. Often it is persons without strong
>> grounding in mathematics and logic who make these conversions,
>> increasing the likelihood of such error.[/color]
>
> You think so? Nulls as formulated in SQL totally defy any standard
> mathematics or logic. Any system that permits the predicate (x=x) to
> evaluate to anything other than true isn't likely to win many votes
> from persons with a strong grounding in mathematics. It is precisely
> because nulls are so counter-intuitive that they lead to so many
> mistakes in SQL. However, I do agree with your basic point that if you
> regularly need to convert nulls like this it may indicate weakness in
> your design or requirements.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
> --
>[/color]

[David Portas]

Re: Any functions to replace NZ in SQL Server?

Rico wrote:[color=blue]
> I probably shouldn't open my mouth in the presence of some posters, but with
> regard to converting nulls being bad design; I have a bunch of reports that
> show loans and payments (just to make things simple). If I have no payment
> record (a null) then I have zero payments applied to the loan. By
> converting these null payment records to zero payments, is this considered
> in theory bad design? Or is this an exception to that rule. Is there a
> definition between what would be considered bad design and what is
> considered an exception?
>
> Not trying to raise a debate really, just asking for clarification.[/color]

If you have no payment record then why do you have a null?

Nulls are a source of complexity and error. On the other hand, avoiding
them can lead to complexity of a different kind - often requiring the
creation of additional tables for example. Whether to use nulls at all
is a controversial topic about which a huge amount has been written and
argued over. In practice, SQL database systems tend to make it very
hard to avoid them altogether.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: Any functions to replace NZ in SQL Server?

Rico (me@you.com) writes:[color=blue]
> I probably shouldn't open my mouth in the presence of some posters, but
> with regard to converting nulls being bad design; I have a bunch of
> reports that show loans and payments (just to make things simple). If I
> have no payment record (a null) then I have zero payments applied to the
> loan. By converting these null payment records to zero payments, is
> this considered in theory bad design? Or is this an exception to that
> rule. Is there a definition between what would be considered bad design
> and what is considered an exception?[/color]

In practice there are many cases where NULL and 0 or the empty string
are more or less the same thing.

Of course, if we have a table:

CREATE TABLE loans (loanno char(11) NOT NULL,
...
no_of_payments int NULL,
....

A NULL in no_of_payments taken to the letter would mean "we don't
know how many payments that has not been done on this loan, if any
at all" or "this is a loan on which you do not make payments at all,
so it is not applicable".

But I don't believe for a second that this is how your table design looks
like. And with a more complex design, there could easily appear a NULL in
a query.

There are many cases were isnull or coalesce comes in handy. For some
computations, equating NULL with 0 makes sense. But coalesce can
also be used to get a value from multiple places. Assume, for instance,
that a customer can have a fixed discount, or he can be part of a
group that can have a common rebate. Assuming that an individual
discount overrides the group discount, that would be:

coalesce(Custom ers.discount, Groups.discount , 0)

The 0 at the end is really needed here, if we assume that a customer
may not belong to any group. That is, the Groups table comes in with
a left join, so it does not help if Groups.discount is not nullable.
And Customers.disco unt needs to be NULL, so we can have some logic
to get the group instead. It would not be good to have 0 to mean
"use group instead", because we may actually want to deprive the
customer of the group rebate.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Lyle Fairfield]

Re: Any functions to replace NZ in SQL Server?

FROM BOL:
A value of NULL indicates that the value is unknown. A value of NULL is
different from an empty or zero value. No two null values are equal.
Comparisons between two null values, or between a NULL and any other
value, return unknown because the value of each NULL is unknown.

Null values generally indicate data that is unknown, not applicable, or
that the data will be added later. For example, a customer's middle
initial may not be known at the time the customer places an order.

Following is information about nulls:

To test for null values in a query, use IS NULL or IS NOT NULL in the
WHERE clause.

When query results are viewed in SQL Server Management Studio Code
editor, null values are shown as (null) in the result set.

Null values can be inserted into a column by explicitly stating NULL in
an INSERT or UPDATE statement, by leaving a column out of an INSERT
statement, or when adding a new column to an existing table by using
the ALTER TABLE statement.

Null values cannot be used for information that is required to
distinguish one row in a table from another row in a table, for
example, foreign or primary keys.

In program code, you can check for null values so that certain
calculations are performed only on rows with valid, or not NULL, data.
For example, a report can print the social security column only if
there is data that is not NULL in the column. Removing null values when
you are performing calculations can be important, because certain
calculations, such as an average, can be inaccurate if NULL columns are
included.

If it is likely that null values are stored in your data and you do not
want null values appearing in your data, you should create queries and
data-modification statements that either remove NULLs or transform them
into some other value.

Important:
To minimize maintenance and possible effects on existing queries or
reports, you should minimize the use of null values. Plan your queries
and data-modification statements so that null values have minimal
effect.

When null values are present in data, logical and comparison operators
can potentially return a third result of UNKNOWN instead of just TRUE
or FALSE. This need for three-valued logic is a source of many
application errors. These tables outline the effect of introducing null
comparisons.

-------
I think that null should not be referred to as a value, in the same way
that celibacy should not be referred to as sex.

In addition, the statements:
"A value of NULL is different from an empty or zero value."
and
"you should create queries and data-modification statements that
either ... or transform them into some other value."
conflict.