Error converting data type varchar to float - MS SQL Server

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
sqldba2013Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Daniel WilsonConnect With a Mentor Commented:
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
 
sqldba2013Author Commented:
Line 15 is giving error.

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

Pls advise.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sqldba2013Author Commented:
I am getting error in line 22 to 26.

Please correct my mistake in above sql query.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
sqldba2013Author Commented:
--
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.