Error converting data type varchar to float.

Question

Post reply

Error converting data type varchar to float.

Nabha

SSCrazy Eights

Points: 8534
More actions
August 28, 2009 at 8:14 am

#134642

Hi All,
Could be a very common one and could be there on the net but I was in a bit hurry and wanted to know why its happening. please help. Thanks.
I am getting this error:
Error converting data type varchar to float. When I run the query,
SELECT CASE
WHEN ColX IS NOT NULL
THEN ColX
WHEN ColY IS NOT NULL
THEN ColY
ELSE 'N/A'
End AS MyCOL,
FROM #unit1
When I remove "ELSE 'N/A'", query would return all the records. Please help. Thanks.
---------------------------------------------------------------------------------

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Use CONVERT to convert ColX and ColY to VARCHAR in your "THEN"-parts

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

Florian Reischl (8/28/2009)
Use CONVERT to convert ColX and ColY to VARCHAR in your "THEN"-parts

Thanks Florian, it works that way.

I was just curious to know what was happening.

Point is this is there in one of my de-normalization stored procedure. It was running well all this while and today it gave this error all of a sudden! I dont have the luxury to change the stored procedure.

Only 0 and Null are there in those two columns. When I do the sp_help of the temp table that I have shown, it indicates me that those two colulmns are of type 'Float'. Appreciate your help regarding this. Thanks.

---------------------------------------------------------------------------------

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

CASE WHEN ColX IS NOT NULL THEN ColX

WHEN ColY IS NOT NULL THEN ColY

ELSE 'N/A'

END

What is happening is that you are trying to return two different datatypes as the result of your CASE statement. A column in SQL, even a result set, can only have one datatype. 'N/A' is CHAR or VARCHAR type, while ColX and ColY are FLOAT.

Obviously you cannot convert the string 'N/A' into a valid floating point number, but you can convert the numeric values in 'ColX' and 'ColY' to character strings.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Converting NULLs to 'N/A' is one of those formatting things for display purposes and it shouldn't be done in the database... it should be done in the GUI or reporting tool if you have one. That being said, you don't need a CASE statement for this and, if you absolutely insist on formating in SQL Server, then you might as well go all the way with proper alignment of decimal places...

The following query replaces the CASE statement...

SELECT COALESCE(STR(ColX,p,d), STR(ColY,p,d), 'N/A') AS MyCol

FROM #Unit 1

Replace "p" with the number of the total number of characters you wish to display the numbers as including decimal places and the "d" with the number of decimal places you want to display.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Sweet.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Heh... glad to see you back in sorts, Bob... although I was getting used to calling you "the Dot formally known as Bob". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nabha SSCrazy Eights Points: 8534 More actions · Answer 7

Thanks all for all your help. Temporarily managed to push the data into the database using your suggestions. Its nice to know that you all explain 'whats really happening' rather than just providing the solution.

Will see what I can do to permanently change this once for all. It was an old code lying there. Thanks again.

Pakki.

---------------------------------------------------------------------------------

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

You're welcome, Pakki.

Jeff, I decided if I kept on changing nicknames in addition to avatars I might give Steve a stroke. So out of consideration for his enlightened and tolerant management style, I went back to using my name.

That could change again, though....

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Bob Hovious (8/31/2009)
Jeff, I decided if I kept on changing nicknames in addition to avatars I might give Steve a stroke. So out of consideration for his enlightened and tolerant management style, I went back to using my name.

Heh... I thought I saw a vein starting bulge in his forehead. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Pakki (8/30/2009)
Thanks all for all your help. Temporarily managed to push the data into the database using your suggestions. Its nice to know that you all explain 'whats really happening' rather than just providing the solution.
Will see what I can do to permanently change this once for all. It was an old code lying there. Thanks again.
Pakki.

Thanks for the feedback, Pakki. A lot of folks won't take the time and I, for one, appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)