[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

[trvishi]

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.

[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

[trvishi]

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.

[winniewang]

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

[trvishi]

No Problem.

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

[winniewang]

Yeah, you are right. Now I can process successfully :-)

Thanks^_^
Winnie

[pdreyer]

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

[winniewang]

Aha, Thanks for your detail info:-)

Thanks^_^
Winnie

[trvishi]

Originally Posted by pdreyer

For more info read:
What Every Computer Scientist Should Know About Floating-Point Arithmetic
Using the float Datatype

Thanks pdreyer