Hallo zusammen...

ich hatte ein Projekt zuerst auf einer MySQL Datenbank und hab jetzt bzw. bin noch dabei alles auf MSSQL umzustellen...

Auf der MySQL konnte ich den folgenden SQL Befehl verwenden:

markieren

SQL-Code:

Select Total_3Months.* From
( Select
(Select If(SUM(ALL_AvailableStock) Is Null, 0, SUM(ALL_AvailableStock)) as ALL_AvailableStock From INV_ALL
Where ALL_PLC = "25" And ALL_StockCoverage1_FreeAvailable > 77.4 And ALL_StockCoverage1_FreeAvailable <= 154.8) as PLC25,
(Select If(SUM(ALL_AvailableStock) Is Null, 0, SUM(ALL_AvailableStock)) as ALL_AvailableStock From INV_ALL
Where ALL_PLC = "30" And ALL_StockCoverage1_FreeAvailable > 77.4 And ALL_StockCoverage1_FreeAvailable <= 154.8) as PLC30,
(Select If(SUM(ALL_AvailableStock) Is Null, 0, SUM(ALL_AvailableStock)) as ALL_AvailableStock From INV_ALL
Where ALL_PLC = "40" And ALL_StockCoverage1_FreeAvailable > 77.4 And ALL_StockCoverage1_FreeAvailable <= 154.8) as PLC40
) as Total_3Months

Wie man sieht verwende ich mehrere Subselects und gebe diese in einer neuen temporären Tabelle aus...
Wenn ich das jetzt auf der MSSQL versuche, scheitere ich daran das ich die Subselect nicht als Spalten ausgeben kann...
Die folgende Fehlermeldung erhalte ich:

Zitat:

Falsche Syntax in der Nähe von 'as PLC25,'

Kann man denn oberen SQL Befehel überhaupt mit der MSSQL Datenbank verwenden, bzw. so umbauen das er funktioniert?

Versuch mal

markieren

SQL-Code:

...Select If(SUM(ALL_AvailableStock) Is Null, 0, SUM(ALL_AvailableStock)) as PLC25 From INV_ALL
Where ALL_PLC = "25" And ALL_StockCoverage1_FreeAvailable > 77.4 And ALL_StockCoverage1_FreeAvailable <= 154.8) ,...

Btw. Es handelt sich hier ume eine DERIVED TABLE und keinen Subselect

Vielleicht noch ein "* from" zwischen "Select (Select" spendieren?

Oder das erste Select in der Klammer weglassen?

Weder noch... So wies aussieht kann man in der MSSQL Temporär keine neuen Tabellen Namen vergeben...

So gehts auf alle Fälle:

zusammenfalten · markieren

SQL-Code:

select
'> 3 Months' as DoH,
'Total' as Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 77.4
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 154.8) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 77.4
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 154.8) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 77.4
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 154.8) PLC40

UNION

select
'> 6 Months' as DoH,
'Total' as Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 154.8
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 309.6) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 154.8
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 309.6) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) > 154.8
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) <= 309.6) PLC40

UNION

select
'> 12 Months' as DoH,
'Total' as Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 309.6) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 309.6) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 309.6) PLC40

UNION

select
'HighStock' as DoH,
'Total' as Brand,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '25'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 154.8
And CONVERT(Float, ALL_AvailableStock) > 200) as PLC25,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '30'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 154.8
And CONVERT(Float, ALL_AvailableStock) > 200) PLC30,

(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]
Where ALL_PLC = '40'
And CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) >= 154.8
And CONVERT(Float, ALL_AvailableStock) > 200) PLC40

Sieht doch Hübsch aus

Nur aus Neugier: Kann denn MSSQL die If-Abfrage in der Form?

Zitat von Jumpy:

Nur aus Neugier: Kann denn MSSQL die If-Abfrage in der Form?

Nee, wenn ich nicht gerade völlig auf dem Schlauch stehe, dann nicht. Bei MSSQL würde man das wohl mit CASE machen.

Jupp IF geht net...
Hab an einer Stelle auch noch ne Abfrage benötigt... Mit CASE gehts dann z.B. so:

markieren

SQL-Code:

(Select CASE WHEN SUM(CONVERT(FLOAT, ALL_AvailableStock)) IS NULL THEN 0 ELSE SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]

Kannst Du denn nicht einfach ein subselect machen, in der die konvertierten Werte stehen und dann einfach eine Pivot-Tabelle?

Basistabelle:

markieren

SQL-Code:

select CONVERT(FLOAT, ALL_AvailableStock) as AvailableStock,
       CONVERT(Float, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.')) as FreeAvailable,
       ALL_PLC
from [inventory].[dbo].[inventory.inv_all]

Klassierungstabelle:

zusammenfalten · markieren

SQL-Code:

select case
         when FreeAvailable between 77.4 amd 154.8 then 3
         when FreeAvailable between 154.8 amd 309.6 then 6
         when FreeAvailable > 309.6 then 12
         else null
       end as DoH,
       case
         when FreeAvailable>154.8 and AvailableStock > 200 then 1
         else null
       end as Highstock,
       AvailableStock,
       FreeAvailable,
       ALL_PLC
from (das-Select-von-eben) X

Fertige Tabelle:
(Zu faul)
Und darum dann deine Summierung, Gruppierung etc.
Das wäre viel übersichtlicher.

Zitat von Andidreas:

(Select CASE WHEN SUM(CONVERT(FLOAT, ALL_AvailableStock)) IS NULL THEN 0 ELSE SUM(CONVERT(FLOAT, ALL_AvailableStock)) as ALL_AvailableStock From [inventory].[dbo].[inventory.inv_all]

Dafür ist COALESCE gedacht:
(Select Coalesce (SUM(CONVERT(FLOAT, ALL_AvailableStock)),0)...

Zitat von Andidreas:

So gehts auf alle Fälle:

ja, aber nur weil etwas geht, hört man doch nicht auf das noch weiter zu optimieren (zumindest nicht, wenn das so krauselig aussieht).

Zitat von Andidreas:

Sieht doch Hübsch aus

Das ist ein Alptraum-Code.



Kann man das nicht schöner realisieren?

zusammenfalten · markieren

SQL-Code:

SELECT DoH,
       'Total' AS Brand,
       SUM(PLC25) PLC25, SUM(PLC30) PLC30, SUM(PLC40) PLC40
FROM (SELECT CASE WHEN plc = '25' THEN stock ELSE 0 END PLC25,
             CASE WHEN plc = '30' THEN stock ELSE 0 END PLC30,
             CASE WHEN plc = '40' THEN stock ELSE 0 END PLC40,
             CASE
               WHEN free > 77.4 AND free <= 154.8 THEN '> 3 Months' 
               WHEN free > 154.8 AND free <= 309.6 THEN '> 6 Months' 
               WHEN free > 309.6 THEN '> 12 Months' 
             END DoH
      FROM (SELECT CONVERT(FLOAT, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.') AS free,
                   ALL_AvailableStock AS stock,
                   ALL_PLC AS plc
            FROM [inventory].[dbo].[inventory.inv_all]
            WHERE ALL_PLC IN ('25', '30', '40')) x) x
GROUP BY DoH

UNION

SELECT 'HighStock' AS DoH,
       'Total' AS Brand,
       SUM(CASE WHEN plc = '25' THEN stock ELSE 0 END) PLC25,
       SUM(CASE WHEN plc = '30' THEN stock ELSE 0 END) PLC30,
       SUM(CASE WHEN plc = '40' THEN stock ELSE 0 END) PLC40
FROM (SELECT CONVERT(FLOAT, REPLACE(ALL_StockCoverage1_FreeAvailable, ',', '.') AS free,
             ALL_AvailableStock AS stock,
             ALL_PLC AS plc
      FROM [inventory].[dbo].[inventory.inv_all]
      WHERE ALL_PLC IN ('25', '30', '40')) x
WHERE free >= 154.8
  AND stock > 200