1

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

1
  • 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 - posess SELECT FORMAT((... as the first UNION's subquery, and finally sort the UNION's result as needed.
    – Akina
    Commented Jul 19, 2019 at 7:34

1 Answer 1

1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.