Laufzeit von Stored Procedure verkürzen

[jobo]

Ich habe den ersten Teil Deiner Unionabfrage umgebaut, so dass schätzungsweise ein Table Scan reicht.
Ist in Oracle gemacht, dürfte aber keine große Rolle spielen. Die Feldtypen habe ich natürlich "optimiert".

Statt der Split Funktion habe ich aus Bequemlichkeit eine normale Tabelle verwendet, geht ja nur um ein Beispiel.
Die Tabelle hat keine Indizes oder Constraints, knapp 1 Mio Datensätze.
Als Test habe ich noch eine Datumsspalte im 2. Select verwendet. Wirst Du wahrscheinlich auch haben / brauchen.

Code:

10:01:02 SQL> select * from fnsplit;
 
BRAND      PRODUCTLINE
---------- -----------
Mercedes   Carrera
Mercedes   B-Klasse
Porsche    Cayenne
Porsche    Boxter
Porsche    Carrera
 
Executed in 0,125 seconds
 
10:01:19 SQL> 
10:01:46 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:01:46 SQL> /
 
STORAGE   TYP  SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs           231      282634      183282       98890          98846      183942         110         999889
 
Executed in 1,155 seconds
 
10:01:50 SQL> delete from fnsplit s where s.brand ='Mercedes';
 
2 rows deleted
 
Executed in 0,047 seconds
 
10:03:05 SQL> 
10:03:22 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:03:22 SQL> /
 
STORAGE   TYP  SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs           231      199650      127974       71214          71170      128590         110         709379
 
Executed in 1,466 seconds
 
10:03:26 SQL> 
10:04:05 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ, to_char(all_date, 'YYMM'),
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2> 0 OR
          28                   ALL_Pir3> 0)
          29                   )x
          30            group by to_char(all_date, 'YYMM')
          31  /
 
STORAGE   TYP  TO_CHAR(ALL_DATE,'YYMM') SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------------------ ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs 1103                                63       49242       31428       17682          17672       31590          30         174355
Warehouse SKUs 1102                                63       44234       28096       16022          16010       28270          30         157647
Warehouse SKUs 1101                                63       54450       34902       19422          19410       35070          30         193467
Warehouse SKUs 1104                                42       51724       33548       18088          18078       33660          20         183910
 
Executed in 0,889 seconds
 
10:04:08 SQL>

[Andidreas]

@jobo

Vielen Dank für deine mühe!
Gehst Du in deiner Lösung davon aus das es Felder wie z.B. "PLC_25", "PLC_30" usw. in der Datenbank gibt?

[jobo]

Nein, ich erzeuge sie über die Case Anweisungen. Eine statische Kreuztabelle sozusagen.
Die Transformation und auch Aggregation (aus Listenform in Spaltenform und Summierung) könnte man allerdings auch in einem Excelmakro durchführen, wenn Excel sowieso als Reportausgabe dient.
Dann muss das SQL jenachdem etwas mehr Daten ausspucken.

[Andidreas]

Ok habs verstanden...
Die Abfrage ist bei mir auch rasend schnell... Aber leider stimmt das Ergebnis nicht... da muss ich nochmal zeit investieren um das richtig hinzubekommen...

Aber die erste Abfrage war auch nicht mein zeitliches Problem...
Ich denke mal deine Lösung hilft mir bei den späteren Abfragen die Mengen sumiert werden z.B. bei WHs Stock

[Andidreas]

@jobo
Die Idee mit der Kreuztabelle ist genial
Ich hab die Stock Berechnungen darauf angepasst... Die dauern jetzt nur noch 8 Sekunden, davor waren es 31 Sekunden!

[jobo]

Zitat von Andidreas:

@jobo
Die Idee mit der Kreuztabelle ist geniall... Aber leider stimmt das Ergebnis nicht...

Ist leider nicht meine Idee
Da ich keine Tabellenvorlage hatte und es eher ums Prinzip ging, ist das korrekte Ergebnis eher Nebensache gewesen. Außerdem wolltest Du ja Laufzeit verkürzen, von richtigem Ergebnis war keine Rede

Ich drück Dir die Daumen

[Andidreas]

Ich hab in meiner großen Stored Procedure die Unions eliminiert und führe jeden Select in einer separaten Stored Procedure durch.
Des Weiteren habe ich die Statements an das Bsp. von jobo (?) angepasst...
Die Laufzeit liegt nun bei ca. 40 Sek.

Anbei die SQL Statements... Evtl. entdeckt jemand noch Verbesserungs möglichkeiten:

SQL-Code:

/* Stored Procedure 1 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ('00', '10', '20') OR Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 1) IN ('#') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('25') THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('30') THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '40' THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ( '50', '55') THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '60' THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '70' THEN 1 ELSE 0 END as PLC_70,
       1 as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
 AND   (ALL_AvailableStock > 0
 OR     ALL_Pir > 0
 OR     ALL_Pir1 > 0
 OR     ALL_Pir2 > 0
 OR     ALL_Pir3 > 0)
Group By (ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU)
) TempTable


/* Stored Procedure 2 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ('00', '10', '20') OR Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 1) IN ('#') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('25') THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('30') THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '40' THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ( '50', '55') THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '60' THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '70' THEN 1 ELSE 0 END as PLC_70,
       1 as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
 AND   (ALL_ClosingStockQTY > 0
 OR     ALL_IssueQTY > 0)
Group By (ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU)
) TempTable


/* Stored Procedure 3 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_AvailableStock ELSE 0 END as PLC_Lower25,
       CASE WHEN ALL_PLC = ('25') THEN ALL_AvailableStock ELSE 0 END as PLC_25,
       CASE WHEN ALL_PLC = ('30') THEN ALL_AvailableStock ELSE 0 END as PLC_30,
       CASE WHEN ALL_PLC = '40' THEN ALL_AvailableStock ELSE 0 END as PLC_40,
       CASE WHEN ALL_PLC IN ( '50', '55') THEN ALL_AvailableStock ELSE 0 END as PLC_50_55,
       CASE WHEN ALL_PLC = '60' THEN ALL_AvailableStock ELSE 0 END as PLC_60,
       CASE WHEN ALL_PLC = '70' THEN ALL_AvailableStock ELSE 0 END as PLC_70,
       ALL_AvailableStock as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 4 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_Lower25,
       CASE WHEN ALL_PLC = ('25') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_25,
       CASE WHEN ALL_PLC = ('30') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_30,
       CASE WHEN ALL_PLC = '40' THEN ALL_ClosingStockQTY ELSE 0 END as PLC_40,
       CASE WHEN ALL_PLC IN ( '50', '55') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_50_55,
       CASE WHEN ALL_PLC = '60' THEN ALL_ClosingStockQTY ELSE 0 END as PLC_60,
       CASE WHEN ALL_PLC = '70' THEN ALL_ClosingStockQTY ELSE 0 END as PLC_70,
       ALL_ClosingStockQTY as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 5 */
Select
CASE WHEN SUM(PIR_Lower25) = 0 THEN 0 ELSE (SUM(Stock_Lower25) / (SUM(PIR_Lower25)/77.4)) END as PLC_Lower25,
CASE WHEN SUM(PIR_25) = 0 THEN 0 ELSE (SUM(Stock_25) / (SUM(PIR_25)/77.4)) END as PLC_25,
CASE WHEN SUM(PIR_30) = 0 THEN 0 ELSE (SUM(Stock_30) / (SUM(PIR_30)/77.4)) END as PLC_30,
CASE WHEN SUM(PIR_40) = 0 THEN 0 ELSE (SUM(Stock_40) / (SUM(PIR_40)/77.4)) END as PLC_40,
CASE WHEN SUM(PIR_50_55) = 0 THEN 0 ELSE (SUM(Stock_50_55) / (SUM(PIR_50_55)/77.4)) END as PLC_50_55,
CASE WHEN SUM(PIR_60) = 0 THEN 0 ELSE (SUM(Stock_60) / (SUM(PIR_60)/77.4)) END as PLC_60,
CASE WHEN SUM(PIR_70) = 0 THEN 0 ELSE (SUM(Stock_70) / (SUM(PIR_70)/77.4)) END as PLC_70,
CASE WHEN SUM(PIR_Total) = 0 THEN 0 ELSE (SUM(Stock_Total) / (SUM(PIR_Total)/77.4)) END as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_Lower25,
      CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_AvailableStock ELSE 0 END as Stock_Lower25,      
      CASE WHEN ALL_PLC IN ('25') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_25,
      CASE WHEN ALL_PLC IN ('25') THEN ALL_AvailableStock ELSE 0 END as Stock_25,      
      CASE WHEN ALL_PLC IN ('30') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_30,
      CASE WHEN ALL_PLC IN ('30') THEN ALL_AvailableStock ELSE 0 END as Stock_30,      
      CASE WHEN ALL_PLC IN ('40') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_40,
      CASE WHEN ALL_PLC IN ('40') THEN ALL_AvailableStock ELSE 0 END as Stock_40,      
      CASE WHEN ALL_PLC IN ('50', '55') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_50_55,
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_AvailableStock ELSE 0 END as Stock_50_55,      
      CASE WHEN ALL_PLC IN ('60') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_60,
      CASE WHEN ALL_PLC IN ('60') THEN ALL_AvailableStock ELSE 0 END as Stock_60,      
      CASE WHEN ALL_PLC IN ('70') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_70,
      CASE WHEN ALL_PLC IN ('70') THEN ALL_AvailableStock ELSE 0 END as Stock_70,      
       (ALL_PIR+ALL_PIR1+ALL_PIR2) as PIR_Total,
       ALL_AvailableStock as Stock_Total       
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 6 */
Select
(SUM(ClosingStock_Lower25) / (SUM(IssueQTY_Lower25)/77.4)) as PLC_Lower25,
(SUM(ClosingStock_25) / (SUM(IssueQTY_25)/77.4)) as PLC_25,
(SUM(ClosingStock_30) / (SUM(IssueQTY_30)/77.4)) as PLC_30,
(SUM(ClosingStock_40) / (SUM(IssueQTY_40)/77.4)) as PLC_40,
(SUM(ClosingStock_50_55) / (SUM(IssueQTY_50_55)/77.4)) as PLC_50_55,
(SUM(ClosingStock_60) / (SUM(IssueQTY_60)/77.4)) as PLC_60,
(SUM(ClosingStock_70) / (SUM(IssueQTY_70)/77.4)) as PLC_70,
(SUM(ClosingStock_Total) / (SUM(IssueQTY_Total)/77.4)) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_Lower25,
      CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_Lower25,      
      CASE WHEN ALL_PLC IN ('25') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_25,
      CASE WHEN ALL_PLC IN ('25') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_25,      
      CASE WHEN ALL_PLC IN ('30') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_30,
      CASE WHEN ALL_PLC IN ('30') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_30,      
      CASE WHEN ALL_PLC IN ('40') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_40,
      CASE WHEN ALL_PLC IN ('40') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_40,      
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_50_55,
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_50_55,      
      CASE WHEN ALL_PLC IN ('60') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_60,
      CASE WHEN ALL_PLC IN ('60') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_60,      
      CASE WHEN ALL_PLC IN ('70') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_70,
      CASE WHEN ALL_PLC IN ('70') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_70,      
       ALL_ClosingStockQTY as ClosingStock_Total,
       ALL_IssueQTY as IssueQTY_Total       
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable

Off Topic
Das man hier als Cretin oder Vollpfosten bezeichnet wird hat mich jetzt nicht so gestört, es ist ja niemand Allwissend (naja manche meines es vielleicht)... Aber das der Werte Herr (oder Dame) arrogant sowas behauptet und dann keine Hilfestellung geben kann finde ich sehr schwach...