SQL Select mit SUM aus einer DB

[JonnyRico]

Wow. Mich erstaunen echt immer wieder die Möglichkeiten von SQL. Die Common Table Expression scheint ne tolle Sache zu sein. Ich weiß allerdings nicht ob es in meinem Fall funktioniert, da ich ein Insert mit Subselect macht. Da habe ich das mit der With-Anweisung nicht hin bekommen. Funktioniert es hier nicht und ich muss HAVING verwenden oder mache was falsch? Vielen Dank für eure Hilfe

Gruß

Sascha

[Fuerchau]

Beispiel eines Insert's mit "With" und glaube mir, der läuft seit V5R1:

-- Einfügen Ergebnis des nachfolgenden Select
-- ==========================================

insert into frct
(FCFIRM,
FCWKNR,
FCDATE,
FCSZNR,
FCLANR,
FCRSIA,
FCRSIB,
FCRSIC,
FCRSID,
FCRSEA,
FCRSEB,
FCRSEC,
FCRSED,
FCRSVA,
FCRSVB,
FCRSVC,
FCRSVD,
FCWCI0,
FCWCI1,
FCWCI2,
FCWCI3,
FCWCI4,
FCWCI5,
FCWCE0,
FCWCE1,
FCWCE2,
FCWCE3,
FCWCE4,
FCWCE5,
FCWCV0,
FCWCV1,
FCWCV2,
FCWCV3,
FCWCV4,
FCWCV5
)


-- Zwischenergebnisse, die im eigentlichen Select benötigt werden

with
-- Ermitteln Positionen Rückstand A Status 05 Inland
XPIA (FCRSIA) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01='05'and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand A Status 05 Export
XPEA (FCRSEA) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01='05'and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand A Status 05 VS
XPVA (FCRSVA) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01='05'and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand B Status 12/15 Prio 50/51 Inland
XPIB (FCRSIB) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and (slprio=50 or slprio=51) and slstat < '80'
),

-- Ermitteln Positionen Rückstand B Status 12/15 Prio 50/51 Export
XPEB (FCRSEB) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and (slprio=50 or slprio=51) and slstat < '80'
),

-- Ermitteln Positionen Rückstand B Status 12/15 Prio 50/51 VS
XPVB (FCRSVB) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and (slprio=50 or slprio=51) and slstat < '80'
),

-- Ermitteln Positionen Rückstand C Status 12/15 Prio 40 Inland
XPIC (FCRSIC) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and slprio=40
),

-- Ermitteln Positionen Rückstand C Status 12/15 Prio 40 Export
XPEC (FCRSEC) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and slprio=40
),

-- Ermitteln Positionen Rückstand C Status 12/15 Prio 40 VS
XPVC (FCRSVC) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and (p1st01='12' or P1ST01='15' ) and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and slprio=40
),

-- Ermitteln Positionen Rückstand D Status >15 <=40 Inland
XPID (FCRSID) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and p1st01>'15' and P1ST01<='40' and kdexkz='I' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand D Status >15 <=40 Export
XPED (FCRSED) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and p1st01>'15' and P1ST01<='40' and kdexkz='E' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Rückstand D Status >12 <=40 VS
XPVD (FCRSVD) as
(select count(*) from afp1, kdst, lgsl
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1firm=slfirm and p1wknr=slwknr
and p1afnr=slauft and p1afhp=slapos
and p1st01>'15' and P1ST01<='40' and kdexkz='V' and p1lanr='D1'
and p1l4da <
year(current date) * 10000 +
month(current date) * 100 +
day(current date)
),

-- Ermitteln Positionen Aktuelle Woche Status 02/04/05/12 Inland
XPWI0 (FCWCI0) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and p1l4da < year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
),

-- Ermitteln Positionen +1 Woche Status 02/04/05/12 Inland
XPWI1 (FCWCI1) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
and p1l4da < year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
),

-- Ermitteln Positionen +2 Woche Status 02/04/05/12 Inland
XPWI2 (FCWCI2) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
and p1l4da < year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
),

-- Ermitteln Positionen +3 Woche Status 02/04/05/12 Inland
XPWI3 (FCWCI3) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
and p1l4da < year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
),

-- Ermitteln Positionen +4 Woche Status 02/04/05/12 Inland
XPWI4 (FCWCI4) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
and p1l4da < year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen >4 Woche Status 02/04/05/12 Inland
XPWI5 (FCWCI5) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='I' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen Aktuelle Woche Status 02/04/05/12 Export
XPWE0 (FCWCE0) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and p1l4da < year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
),

-- Ermitteln Positionen +1 Woche Status 02/04/05/12 Export
XPWE1 (FCWCE1) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
and p1l4da < year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
),

-- Ermitteln Positionen +2 Woche Status 02/04/05/12 Export
XPWE2 (FCWCE2) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
and p1l4da < year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
),

-- Ermitteln Positionen +3 Woche Status 02/04/05/12 Export
XPWE3 (FCWCE3) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
and p1l4da < year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
),

-- Ermitteln Positionen +4 Woche Status 02/04/05/12 Export
XPWE4 (FCWCE4) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
and p1l4da < year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen >4 Woche Status 02/04/05/12 Export
XPWE5 (FCWCE5) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='E' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen Aktuelle Woche Status 02/04/05/12 VS
XPWV0 (FCWCV0) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(current date) * 10000 +
month(current date) * 100 +
day(current date)
and p1l4da < year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
),

-- Ermitteln Positionen +1 Woche Status 02/04/05/12 VS
XPWV1 (FCWCV1) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+7)) * 10000 +
month(date(days(current date)+7)) * 100 +
day(date(days(current date)+7))
and p1l4da < year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
),

-- Ermitteln Positionen +2 Woche Status 02/04/05/12 VS
XPWV2 (FCWCV2) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+14)) * 10000 +
month(date(days(current date)+14)) * 100 +
day(date(days(current date)+14))
and p1l4da < year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
),

-- Ermitteln Positionen +3 Woche Status 02/04/05/12 VS
XPWV3 (FCWCV3) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+21)) * 10000 +
month(date(days(current date)+21)) * 100 +
day(date(days(current date)+21))
and p1l4da < year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
),

-- Ermitteln Positionen +4 Woche Status 02/04/05/12 VS
XPWV4 (FCWCV4) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+28)) * 10000 +
month(date(days(current date)+28)) * 100 +
day(date(days(current date)+28))
and p1l4da < year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),

-- Ermitteln Positionen >4 Woche Status 02/04/05/12 Export
XPWV5 (FCWCV5) as
(select count(*) from afp1, kdst
where p1firm=kdfirm and p1wknr=kdwknr
and p1akdn=kdkdnr and p1avsn=kdvenr
and p1st01>='02' and P1ST01<='15'
and p1st01<>'03' and kdexkz='V' and p1lanr='D1'
and p1l4da >=year(date(days(current date)+35)) * 10000 +
month(date(days(current date)+35)) * 100 +
day(date(days(current date)+35))
),


-- nächste Laufende Nummer
XLFNR (FCFIRM, FCWKNR, FCDATE, FCSZNR, FCLANR) as
(select '2', '001',
year(current date) * 10000 +
month(current date) * 100 +
day(current date), max(FCSZNR) + 1, 'D1'
from frct
where fcfirm='2' and fcwknr='001' and fclanr ='D1'
)
(select '2', '001',
year(current date) * 10000 +
month(current date) * 100 +
day(current date), max(FCSZNR) + 1, 'D1'
from frct
where fcfirm='2' and fcwknr='001' and fclanr ='D1'
)

-- Zusammenführen aller Daten in 1 Zeile
-- =====================================

select * from XLFNR, XPIA, XPIB, XPIC, XPID,
XPEA, XPEB, XPEC, XPED,
XPVA, XPVB, XPVC, XPVD,
XPWI0, XPWI1, XPWI2, XPWI3, XPWI4, XPWI5,
XPWE0, XPWE1, XPWE2, XPWE3, XPWE4, XPWE5,
XPWV0, XPWV1, XPWV2, XPWV3, XPWV4, XPWV5

[woki]

Hallo Forum,
dieses Thema hat mir schon weitergeholfen, ich habe aber noch folgendes Problem:

Mit diesem SQL-Statement bekomme ich die Anzahl der Rechnungen pro Kunde für das Jahr 2006

Select 'AR'  , KZ05RG, KZ06RG, PROGRG, 
       VKGBRG, KDA5RG,             
      (CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END), 
       RGJJRG,
       Count(distinct(BENRRG))
From     INFRGP
Where    RGJJRG = 2006
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG, 
         KDA5RG, KDI8RG, KDA8RG, RGJJRG 

Bei dem Versuch, die Anzahl der Rechnungen pro Monat (RGMMRG) herauszufiltern, scheiter ich.
Wie würde das aussehen?

[Fuerchau]

Select 'AR' , KZ05RG, KZ06RG, PROGRG,
VKGBRG, KDA5RG,
(CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END),
RGJJRG, RGMMRG
Count(distinct(BENRRG))
From INFRGP
Where RGJJRG = 2006
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG,
KDA5RG, KDI8RG, KDA8RG, RGJJRG, RGMMRG

[woki]

Ich möchte nicht für jeden Monat einen Datensatz, sondern die Anzahl der Rechnungen für jeden Monat in einem Datensatz.
Als Beispiel hier der die Summes des Warenwerts für jeden Monat und Gesamtsumme:

Select 'UI'  , KZ05RG, KZ06RG, PROGRG, 
       VKGBRG, KDA5RG,             
      (CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END), 
       RGJJRG,
       Sum(Case When RGMMRG =  1 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  2 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  3 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  4 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  5 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  6 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  7 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  8 then BWESRG else 0 End),
       Sum(Case When RGMMRG =  9 then BWESRG else 0 End),
       Sum(Case When RGMMRG = 10 then BWESRG else 0 End),
       Sum(Case When RGMMRG = 11 then BWESRG else 0 End),
       Sum(Case When RGMMRG = 12 then BWESRG else 0 End),
       Sum(BWESRG) as GesamtSumme
From     INFRGP
Where    RGJJRG = 2006
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG, 
         KDA5RG, KDI8RG, KDA8RG, RGJJRG 

Hier sagt SQL/400 "Spalte RGMMRG oder Ausdruck in SELECT-Liste nicht gültig."

 Select 'AR'  , KZ05RG, KZ06RG, PROGRG,                              
       VKGBRG, KDA5RG,                                              
      (CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END),          
       RGJJRG,                                                      
      (Case When RGMMRG =  1 then Count(distinct(BENRRG)) else 0 End),
       Count(distinct(BENRRG))                                      
From     INFRGP                                                     
Where    RGJJRG = 2006                                              
Group by KZ05RG, KZ06RG, PROGRG, VKGBRG,                            
         KDA5RG, KDI8RG, KDA8RG, RGJJRG 

Da muß es doch eine Lösung geben....

[Fuerchau]

Wenn du Group By verwendest, musst du alle Felder aufführen, die keine Aggregat-Funktion haben:

'UI' ,
(CASE WHEN KDI8RG <> 0 THEN KDI8RG ELSE KDA8RG END),

Spalten, die nicht selektiert sind, dürfen in Group By auch nicht vorkommen:

KDI8RG, KDA8RG

Der Syntax-Checker liefert da ggf. auch mal falsche Hinweise.
Ist das Feld RGMMRG denn auch tatsächlich in der Datei ?

[Fuerchau]

count(distinct (Case When RGMMRG = 1 then BENRRG else NULL End)),

Die Aggregatfunktion muss aussen liegen !
NULL wird nicht mitgezählt.

[woki]

count(distinct (Case When RGMMRG = 1 then BENRRG else NULL End)),

Die Aggregatfunktion muss aussen liegen !
NULL wird nicht mitgezählt.

Super, das war's!!!