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! 16 55327
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: 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!
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
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" <hu**@perFact.REMOVETHIS.info.INVALID> wrote in message
news:j7********************************@4ax.com... 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:
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!
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
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: 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?
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
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: 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?
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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
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,Value) or
corresponding Coalesce function.
[Yes, I've probably posted IsNull(Field,Value) solutions here; that was
then; this is now.]
Excellent! Thanks!
"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info.INVALID> wrote in message
news:vt********************************@4ax.com... 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:
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?
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
On 20 Apr 2006 15:57:53 -0700, Lyle Fairfield wrote: Null is not zero. Null is not a zero length string.
I believe that nulls were not designed to be placeholders for these values.
(snip)
Hi Lyle,
Thus far, I agree with yoour post.
(snip) 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.
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 (UserSpecifiedColumn, DefaultValue) in any query or view. IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or corresponding Coalesce function.
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
Lyle Fairfield wrote: 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.
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
--
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" <RE****************************@acm.org> wrote in message
news:11*********************@z34g2000cwc.googlegro ups.com... Lyle Fairfield wrote: 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.
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 --
Rico wrote: 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.
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/m...S,SQL.90).aspx
--
Rico (me@you.com) writes: 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?
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(Customers.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.discount 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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: ebobnar |
last post by:
I'm having trouble navigating my directory structure using php's ftp
functions (such as ftp_chdir and ftp_cdup.) I'm writing a program that
allows a user to easily create robot.txt files and upload...
|
by: hokiegal99 |
last post by:
A few questions about the following code. How would I "wrap" this in a
function, and do I need to?
Also, how can I make the code smart enough to realize that when a file
has 2 or more bad...
|
by: Barnes |
last post by:
Does anyone know of a good way to use the JavaScript string.replace() method in an ASP form?
Here is the scenario:
I have a form that cannot accept apostrophes. I want to use the replace() so...
|
by: Anthony Jones |
last post by:
Just a bit of background: I'm one of a group of FORTRAN programmers, looking
to switch to C++. We are trying to write a few simple examples to
demonstrate the power of the language to our manager,...
|
by: Roger Redford |
last post by:
Dear Experts,
I'm attempting to marry a system to an Oracle 817 datbase.
Oracle is my specialty, the back end mainly, so I don't
know much about java or javascript.
The system uses javascript...
|
by: tshad |
last post by:
I am setting up some of my functions in a class called MyFunctions.
I am not clear as to the best time to set a function as Shared and when not
to. For example, I have the following bit...
|
by: Tony Rice |
last post by:
If I've loaded a set of functions in a webdocument like this:
<script language="JavaScript" type="text/javascript"
src="/js/foo.js"</script>
Shouldn't I be able to call a function from...
|
by: Rico |
last post by:
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(,0)) to return a zero...
|
by: debasisdas |
last post by:
This thread contains some useful tips/samples regarding FUNCTIONS in oracle, that the forum members may find useful.
FUNCTION:
==========
1.IT IS A COMPILED BLOCK OF CODE WHICH IS STORED AS AN...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |