Sql-server – Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to float

errorssql serverunion

I created some select statements which run without any problem in SQL Server. Then I joined them together with UNION and got this error message>

Msg 8114, Level 16, State 5, Line 1 Error converting data type
nvarchar to float. Warning: Null value is eliminated by an aggregate
or other SET operation.

Does anyone have an idea why? (I googled, and found that it could because I have some NULL values, which can-t be summed up or something, but as I said, the two codes run without error seperately.)

SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) 
       AND (YEAR(BuchDatum) = 2019) 
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) 
       AND (YEAR(BuchDatum) = 2019) 
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Restzeit_T)) AS Zukünftliche_Bearbeitungszeiten, 3 AS filtering
FROM   [Test].[dbo].[ZNEJO_DETAIL]
WHERE  (ArbPlatz = 103100) 
       AND (YEAR(Endtermin) = 2019) 
       AND (DATEPART(week, Endtermin) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) 
       AND (YEAR(BuchDatum) = 2019) 
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(Angebot) AS Angebot, 5 AS filtering
FROM   [Test].[dbo].[CM01_DETAIL]
WHERE  (Woche = '21.2019') 
       AND (Arbeitsplatz = '103100')
UNION 
-- This is where I seperate the code. 
SELECT FORMAT((
          SELECT W21
          FROM [Test].[dbo].[Übersicht_Woche_RAP_103100]
          WHERE id = 2) /
          (SELECT W21
          FROM [Test].[dbo].[Übersicht_Woche_RAP_103100]
          WHERE id = 5),'P0') AS Calculation, 6 AS filtering

The two screenshots are the two results from the code run in two parts. I want to join them together with UNION.
Part 1 Part2

Thanks for all the help

Best Answer

Tha sollution is (Thanks @Akina) To use the CAST() functino and convert all the select statements to datatype CHAR.

Sample code:

SELECT CAST(SUM(try_convert(numeric(38, 2), Rüsten_Ist)) AS CHAR) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = DATEPART(week,GETDATE())-8)