Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: CASE WHEN IsError?

    Can I construct a view that converts a Char to a Number even when the value of the Char does not have a recognizable number (and return 0 in that case)?

    Here is a couple examples of what I'm trying to do as part of my select:
    CONVERT (Money, REPLACE(ISNULL(UserDefined5, '0'), ' ', ''), 1) AS ListPrice,
    CONVERT (Decimal(2), REPLACE(ISNULL(UserDefined28, '0'), ' ', '')) AS LaborHours

    Unfortunately, in the UserDefinedField area of the database, all the fields are Char (this is part of an MRP app where I cannot change the datatype).

    Here's what I'd like to do:
    SELECT CASE WHEN ISERROR(CONVERT (Money, REPLACE(ISNULL(UserDefined5, '0'), ' ', ''), 1)) THEN 0 ELSE CONVERT (Money, REPLACE(ISNULL(UserDefined5, '0'), ' ', ''), 1) END AS ListPrice,
    SELECT CASE WHEN ISERROR(CONVERT (Decimal(2), REPLACE(ISNULL(UserDefined28, '0'), ' ', ''))) THEN 0 ELSE CONVERT (Decimal(2), REPLACE(ISNULL(UserDefined28, '0'), ' ', '')) END AS LaborHours

    Of course, there is no "IsError" function in SQL.

    It works fine in Access; heck, in Access, all I need is the IsNull and Val functions!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT CASE WHEN ISNUMERIC(UserDefined5) = 0 
    	THEN 0 
    	ELSE CONVERT (Money, REPLACE(UserDefined5, ' ', ''), 1) 
    END AS ListPrice
    
    SELECT CASE WHEN ISNUMERIC(UserDefined5) = 0 
    	THEN 0 
    	ELSE CONVERT (Decimal(2), REPLACE(UserDefined28, ' ', ''))
    END AS LaborHours
    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

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Nice - thanks! Sometimes I miss the obvious by trying to be clever.
    I'd definitely give you a beer for that.


    PS - I got distracted for a few moments on Grabel's law in your signature

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    With the ISNUMERIC function, which already checks to see if SQL Server can cast the data as a number, I don't need the REPLACE function, do I?

    If the ISNUMERIC was successful, so will the REPLACE be successful, right?

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Sometimes it can be faster to just open Management Studio and try it than to ask it on the forum.
    Code:
    SELECT CASE WHEN ISNUMERIC('  6   ') = 0 
    	THEN 0 
    	ELSE CONVERT (Money, '  6   ', 1) 
    END
    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
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Sorry for the distraction and thanks for the beer.

    I have changed my signature accordingly. 8 beers so far
    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

Posting Permissions

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