Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: IsNull for decimal acting up

    When I run this code on a column of the type float or real it's ok, but not if the column is decimal, why?

    this produces an error:

    SELECT IsNull(column1,'') as column1
    FROM mytable

    ---

    this code works:

    SELECT IsNull(column1,0) as column1
    FROM mytable

    ---

    The problem is that this query is used in a C++ environment to build an insert into another table. The query is build together in a CString.
    C++ considers a CString to have ended if it encounters a NULL, therefore I need the check on all columns.

    The error message I get is:

    "Error converting data type varchar to numeric."

    Why is it ok to use float or rel, but not decimal??

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...I wouldn't think to use an empty string for a numeric in any case...but you're right...and I don't know why

    DECLARE @x float, @y decimal(3,2)
    SELECT @x = 1.23, @y = 1.23
    SELECT @x, @y
    SELECT COALESCE(@x,''), COALESCE(@y,'')
    SELECT COALESCE(@x,0), COALESCE(@y,0)
    SELECT @x = null, @y = null
    SELECT @x, @y
    SELECT COALESCE(@x,'')
    SELECT COALESCE(@y,'')
    SELECT COALESCE(@x,0)
    SELECT COALESCE(@y,0)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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