Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    13

    Unanswered: Nz - Null to Zero function

    Hi,

    Nz(variant [, valueifnull ] ) is the syntax.

    If the value of variant isn't Null, then the Nz function returns the value of variant.

    Can i return a custom value if the variant isnt null? are there any other commands that might suit this purpose?

    Thanks all!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT CASE WHEN variant IS NOT NULL THEN customvalue END as tada
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    13
    Hi,

    will try that out. Now what if the "variant" used in the query above is an Alias column name calculated in the same SELECT statement?

    wg. SELECT Nz(Variant1,column1-column2) as Tempcolumn FROM Table;

    Thanx
    Last edited by nikhilstephen; 02-18-12 at 09:58.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    By the way; "Nz" is not a function in SQL Server.

    Have you tried to use the example code I have posted? You should be able to modify this yourself without too much trouble.

    Give it a go and if you are still struggling then post your attempts and we can help you solve it
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by nikhilstephen View Post
    Hi,

    Nz(variant [, valueifnull ] ) is the syntax.

    If the value of variant isn't Null, then the Nz function returns the value of variant.

    Can i return a custom value if the variant isnt null? are there any other commands that might suit this purpose?
    The correct syntax is
    COALESCE (value1, value2, value3, ...)
    Meaning: return vaulue1 unless it IS NULL, then take value2 unless it IS NULL, then take value3 unless....
    In other words, return the first NOT NULL value. When all values are NULL, return NULL.

    All the data types must be compatible, so you can not write
    SELECT COALESCE(wage, 'no wage specified') as wage -- one can not have both numerical and alphanumerical values in one column
    but
    SELECT COALESCE(wage, 0.0) as wage -- all numerical values
    or
    SELECT COALESCE(CAST(wage as VARCHAR), 'no wage specified') as wage -- all alphanumerical values
    Can i return a custom value if the variant isnt null?
    No, when the variant isn't NULL, the value of the variant will be returned, by definition.
    Last edited by Wim; 02-18-12 at 18:42.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2012
    Posts
    13
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •