sqldba2013
asked on
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.
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
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;
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am getting error in line 22 to 26.
Please correct my mistake in above sql query.
Please correct my mistake in above sql query.
>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.
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.
ASKER
--
ASKER
I tried to convert columns to float but still I am getting data type conversion error.
Pls advise.