Solved

Error converting data type varchar to float - MS SQL Server

Posted on 2013-12-06
6
7,709 Views
Last Modified: 2013-12-09
I am facing data type conversation error while executing below select statement. Please advise how to fix below error.

SELECT 
CONVERT(VARCHAR(10), #SVC.ID1) As ID1,
'Batch,' + 'ID1,' + CONVERT(VARCHAR(10), #SVC.ID1) + ',SVC' AS Batch,
'Urgent request,' + 'ID1,' + CONVERT(VARCHAR(10), #SVC.ID1) + ',SVC' AS [Urgent request],
'Total of Lines,' + 'ID1,' + CONVERT(VARCHAR(10), #SVC.ID1) + ',SVC' AS [Total of Lines],
'Total quote value,' + 'ID1,' +CONVERT(VARCHAR(10), #SVC.ID1) + ',SVC' AS [Total quote value],
'PO Value,' + 'ID1,' + CONVERT(VARCHAR(10), #SVC.ID1) + ',SVC' AS [PO Value]
INTO #SVCSummary
FROM #SVC
GROUP BY [#SVC].[ID1],
#SVC.[Total of Lines],
#SVC.[Total quote value]
ORDER BY 
#SVC.[ID1] ASC;
SELECT Batch,
[Urgent Request],
[Total of Lines],
[Total Quote Value],
[PO Value]
FROM #HW
UNION 
SELECT Batch,
[Urgent Request],
[Total of Lines],
[Total Quote Value],
[PO Value]
FROM #SVCSummary;

Open in new window

Data type details:
a. Batch column (float,null)
b. ID1 column (float,null)
c. [Urgent Request] column (bit not null)
d. [Total of Lines] column (float null)
e. [Total Quote Value] column (money null)
f. [PO Value] column  (float null)

Error:
Error converting data type varchar to float
0
Comment
Question by:sqldba2013
  • 3
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 125 total points
ID: 39701406
>SELECT  CONVERT(VARCHAR(10), #SVC.ID1) As ID1,
> 'Batch,' + 'ID1,' + CONVERT(VARCHAR(10), #SVC.ID1) + ',SVC' AS Batch,
fyi the #SVC.ID1 references will be as whatever data type ID1 is, and not the above-converted varchar.
 
Looking at the second SELECT that has the UNION, the entire set will be based on the data types of the first part of the UNION ( #HW ), which means that the columns in the second part of the UNION ( #SVCSummary ) must be the same type.  Eyeball these tables to see if there are any data type differences.
0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 125 total points
ID: 39701411
Which select statement is giving you the error?  the one starting on line 1 or on line 15?

Are floats coming out of #HW for [Total of Lines] or [Total Quote Value]?  If so, wrap them in CONVERT statements like you did for many values in the first query.
0
 

Author Comment

by:sqldba2013
ID: 39702645
Line 15 is giving error.

I tried to convert columns to float but still I am getting data type conversion error.

Pls advise.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:sqldba2013
ID: 39702764
I am getting error in line 22 to 26.

Please correct my mistake in above sql query.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39703103
>I am getting error in line 22 to 26.
Eyeball the column data types in tables #HW and #SVCSummary, and tell us if for any column the data type is different.

>Error converting data type varchar to float
I'm thinking that for a column #HW is a float, and for #SVCSummary it's a varchar.
0
 

Author Closing Comment

by:sqldba2013
ID: 39705779
--
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now