Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: Who can help me to check the syntax?

    Hi Everyone,

    I got the error below when I processed a dimension:

    OLE DB error: OLE DB or ODBC error: Insufficient result space for explicit conversion of FLOAT NULL value '2.2400000000000002' to a CHAR field.

    In the dimension, I have a column defined like this:
    Case WHEN Weight IS NULL THEN 'Unknown' ELSE CONVERT(CHAR (5), Weight) END AS [WeightDesc]

    Does it mean that there are something wrong with my query?
    Who can helpe me to check it?
    Your help will be appreciated.

    Thanks^_^
    Winnie

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by winniewang
    Hi Everyone,

    I got the error below when I processed a dimension:

    OLE DB error: OLE DB or ODBC error: Insufficient result space for explicit conversion of FLOAT NULL value '2.2400000000000002' to a CHAR field.

    In the dimension, I have a column defined like this:
    Case WHEN Weight IS NULL THEN 'Unknown' ELSE CONVERT(CHAR (5), Weight) END AS [WeightDesc]

    Does it mean that there are something wrong with my query?
    Who can helpe me to check it?
    Your help will be appreciated.

    Thanks^_^
    Winnie
    The syntax is right, but there seems to be a float value '2.2400000000000002' which cant be converted to a char(5). Either Remove the CHAR(5) or Use the round function to truncate the decimals.

  3. #3
    Join Date
    Apr 2007
    Posts
    63
    Actually, the "Weight" column is float, and the values are NULL, 230.0, 231.4...., I didn't find the value '2.2400000000000002', I can remove the Char(5), But If I want to convert a column like "Weight" to Char(5), are there other mothod?

    Thanks^_^
    Winnie

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by winniewang
    Actually, the "Weight" column is float, and the values are NULL, 230.0, 231.4...., I didn't find the value '2.2400000000000002', I can remove the Char(5), But If I want to convert a column like "Weight" to Char(5), are there other mothod?

    Thanks^_^
    Winnie
    Hmm I dont remember it exactly, but floats are OS dependant I think and can vary and thats why its probably better to use numeric. You might want to do some research on that.

    Try doing something this. convert(char,convert(numeric(3,2),column))

    3,2 is precision and scale.

    Also, read up on the convert function.

  5. #5
    Join Date
    Apr 2007
    Posts
    63
    Thank you for your quick reply:-)
    I will reread Convert function and try you suggestion tomorrow. it is the time to go home, Thank you for your reply.

    Thanks^_^
    Winnie

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    No Problem.

    Its either 3,2 or 5,2 for your problem.

  7. #7
    Join Date
    Apr 2007
    Posts
    63
    Yeah, you are right. Now I can process successfully :-)

    Thanks^_^
    Winnie

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by trvishi
    Hmm I dont remember it exactly, but floats are OS dependant I think and can vary and thats why its probably better to use numeric. You might want to do some research on that.
    For more info read:
    What Every Computer Scientist Should Know About Floating-Point Arithmetic
    Using the float Datatype

  9. #9
    Join Date
    Apr 2007
    Posts
    63
    Aha, Thanks for your detail info:-)

    Thanks^_^
    Winnie

  10. #10
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Thanks pdreyer

Posting Permissions

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