0

I'm quite new to SQL programming, so i apologize in Advance for all the irrelevant Information and stupid Questions :)

I have a database in Microsoft SQL Server Management Studio.

I select data from multiple tables in the database according to different criterias. I join these datas toigether with UNION. Because UNION has its own order, I also created a column in each SELECT statement called filtering, and then at the end of the UNION I ORDERed the data BY this column> filtering. Then I insert my results into a table I have previously created. Until now everything functions perfectly. Then I close the statement, and start a new one with basically almost the same information. (Only the weekday changes from 21 to 22). I also insert these records into the new table, and there I have my "error".

Becuase of the ORDER BY column (filtering) these records are inserted into row 4, 5 and 6, instead of 1, 2 and 3.

Is there a waz to correct this, for example not to display the filtering column, or JOIN by the filtering column?

As I said I'm quite new to SQL (started learning it about about 2 Weeks ago) any help is highly appreciated!

My code and the results

DELETE FROM [Test].[dbo].[Übersicht_Woche_RAP]
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, id)
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), 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)
ORDER BY filtering;

INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W22, id)
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) = 22)
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) = 22)
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) = 22)
ORDER BY filtering

enter image description here

  • and then at the end of the UNION I ORDERed the data BY this column> filtering The table is a unordered heap. So your ORDER BY filtering makes no sense and must be removed. Becuase of the ORDER BY column (filtering) these records are inserted into row 4, 5 and 6, instead of 1, 2 and 3. INSERT INTO cannot insert data in existing row (update this row), it always adds rows into table. You must use UPDATE - but you have lost your 'filtering' value from 1st query, so the task is unsolvable by such way. – Akina Jul 16 at 9:03
  • Pick which engine you will use; SQL Server and MySQL are quite different. – Rick James Jul 16 at 22:39
0

The variant with direct separate SELECTs:

WITH
cte21 AS (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), 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)
         ),
cte22 AS (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) = 22)
          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) = 22)
          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) = 22)
         )
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, W22, id)
SELECT cte21.Rüsten, cte22.Rüsten, cte21.filtering
FROM cte21
JOIN cte22 ON cte21.filtering = cte22.filtering

But you can create more compact version by create cte AS (SELECT 21 weeknumber UNION SELECT 22) and cross apply it to formalized version of your query (add GROUP BY and wrap values with MAX(), or use PIVOT).

  • Thanks so much for your fast Reply! It works perfect! Is there any way I can add Delete Statement before this Code, so it clears all the data in the table already, before inserting new values? – Neymar0001 Jul 16 at 9:16
  • @Neymar0001 That is your data, it is up to you to decide what and when to do with them. You need to truncate table data before? add DELETE before... – Akina Jul 16 at 9:19
  • Thanks everyone! I tried that, but forgot to put ";" at the end of the delete Statement, and I received an error message. (Yes i know, I'm a stupid beginner :)). I corrected that mistake, and now everything works perfectly. – Neymar0001 Jul 16 at 9:27
  • People give me crap when I start CTEs with ;WITH ... - this is another prime example why I do that. – Aaron Bertrand Jul 16 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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