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.
Thanks for all the help
SELECT CAST(SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS CHAR) AS Rüsten, 1 AS filtering ...
and the same for all another SUMs. Alternatively - posessSELECT FORMAT((...
as the first UNION's subquery, and finally sort the UNION's result as needed.