Subselect mit MSSQL

[Andidreas]

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:

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?

[mkinzler]

Versuch mal

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

[jobo]

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

[DeddyH]

Oder das erste Select in der Klammer weglassen?

[Andidreas]

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

So gehts auf alle Fälle:

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

[Jumpy]

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

[Bbommel]

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.

[Andidreas]

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

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]

[Iwo Asnet]

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

Basistabelle:

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:

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)...

[omata]

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?

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