dynamisches Group by funktioniert nicht

[Tobse77]

Hallo zusammen,

möchte in meinem Dialogprogramm dem Anwender die Möglichkeit anbieten, unter beliebligen Kumulationskritieren auszuwählen (auch Mehrfachauswahl gültig).

Hierzu habe ich sowohl die Select-Felder, als auch die Group by-Felder in case-Anweisungen gesetzt (group by habe ich von Select kopiert, so dass ich keine Felder vergesse, oder mich vertippe)
Leider erscheint nach jedem Versuch, eine Kumulation vorzunehmen der SQL-Code -122 (A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND A COLUMN FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE )

Da ich ja die kompletten Select-Felder - natürlich bis auf die Summierungen - in die Group by-Anweisung kopiert habe, kann ich mir diesen Fehler nicht erklären.

Anbei das ausgeführte Select-Statement.
Hinweis: Bei den aufgeführten Hostvariablen beginnend mit :IndKum... handelt es sich um Boolean-Variablen, die entsprechend der Wahl des Anwenders gefüllt sind.

Exec SQL
Insert into VOGIO/WUVT0547
Select
case when :IndKumVb = '1' or :IndKumKd = '1' then 0 else vsman end,
case when :IndKumVb = '1' or :IndKumKd = '1' then vsvbnr else 0 end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana1 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana2 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.acoi else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.apla else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.aort else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.astr else ' ' end,
case when :IndKumKd = '1' then vskdnr else 0 end,
case when :IndKumKd = '1' then kdslst else 0 end,
case when :IndKumKd = '1' then b.ana1 else ' ' end,
case when :IndKumKd = '1' then b.ana2 else ' ' end,
case when :IndKumKd = '1' or :IndKumLd = '1' then b.acoi else ' ' end,
case when :IndKumKd = '1' then b.apla else ' ' end,
case when :IndKumKd = '1' then b.aort else ' ' end,
case when :IndKumKd = '1' then b.astr else ' ' end,
case when :IndKumKd = '1' then vskdgr else ' ' end,
case when :IndKumKd = '1' then c.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslbr else ' ' end,
case when :IndKumKd = '1' then d.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslpt else ' ' end,
case when :IndKumKd = '1' or :IndKumVt = '1' then vsvtr else 0 end,
case when :IndKumKd = '1' or :IndKumVt = '1' then e.tabbez else ' '
end,
case when :IndKumKd = '1' then kdsbdw else ' ' end,
case when :IndKumKd = '1' then kdfu1 else 0 end,
// Teilenr, Bez, Status, Eigen-/Fremdfertigung, Prod.EAN, Karton.EAN, Zolltarif-Nr.
case when :IndKumTn = '1' then vstnr else ' ' end,
case when :IndKumTn = '1' then imdsc else ' ' end,
case when :IndKumTn = '1' then imsts else ' ' end,
case when :IndKumTn = '1' then imcode else ' ' end,
case when :IndKumTn = '1' then imrsv9 else ' ' end,
case when :IndKumTn = '1' then imrsv0 else ' ' end,
case when :IndKumTn = '1' then mi1swn else ' ' end,
// Preisgr, MarkenGruppe
case when :IndKumTn = '1' or :IndKumPr = '1' then vspgrg else ' ' end,
case when :IndKumTn = '1' or :IndKumPr = '1' then f.prbez else ' '
end,
case when :IndKumTn = '1' then vsmagr else ' ' end,
case when :IndKumTn = '1' then g.tabbez else ' ' end,
// MarkenHptGr, MarkeInd, Warengruppe
case when :IndKumTn = '1' then mi1v22 else ' ' end,
case when :IndKumTn = '1' then h.tabbez else ' ' end,
case when :IndKumTn = '1' then vsmark else ' ' end,
case when :IndKumTn = '1' then i.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then vsmrze else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then j.tabbez else ' '
end,
// ProdHptGr, ProdGr
case when :IndKumTn = '1' then vsprhg else ' ' end,
case when :IndKumTn = '1' then p.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then vsprgr else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then k.tabbez else ' '
end,
sum(vvimg3), sum(vvimg2), sum(vvimg1), sum(vvimg0),
sum(vviwg3), sum(vviwg2), sum(vviwg1), sum(vviwg0) 
From VOGIO/wuStatistik Left Outer Join DCWD/AADRNU a
on vsvbnr = a.anum
Left Outer Join DCWD/AADRNU b
on vskdnr = b.anum
Left Outer Join VOGIO/KUNDST00
on vskdnr = kdnr
Left Outer Join VOGIO/V#TAK100 c
on vskdgr = c.tabinn
Left Outer Join VOGIO/V#TABR00 d
on kdslbr = d.tabinn
Left Outer Join VOGIO/V#TAVT00 e
on vsvtr = e.tabinn
Left Outer Join SIM400MFG/FKITMSTR
on vstnr = impn
Left Outer Join DCWD/MITEM
on vstnr = mi1mnr
Left Outer Join VOGIO/V#TAP999 f
on kdslpt = f.sort and vspgrg = f.preisg
Left Outer Join VOGIO/V#TAMG00 g
on vsmagr = g.tabind
Left Outer Join VOGIO/V#TAMH00 h
on mi1v22 = h.tabind
Left Outer Join VOGIO/V#TAMA00 i
on vsmark = i.tabind
Left Outer Join VOGIO/V#TAWG00 j
on vsmrze = j.tabinz
Left Outer Join VOGIO/V#TAPH00 p
on vsprhg = p.tabind
Left Outer Join VOGIO/V#TAPG00 k
on vsprgr = k.tabind
Left Outer Join VOGIO/V#TAPU00 l
on vsprug = l.tabind
Left Outer Join VOGIO/V#TAGB00 m
on vsgebi = m.tabind
Left Outer Join VOGIO/V#TATH00 n
on vsgeba = n.tabind
Left Outer Join VOGIO/V#TALI00 o
on mi1v19 = o.tabinz
Where
Case When :ikdgr <> 0 Then :ikdgr Else vskdgr end = vskdgr
And Case When :ikdbr <> 0 Then :ikdbr Else kdslbr end = kdslbr
And Case When :ikdland <> ' ' Then :ikdland Else vsbla end = vsbla
And vsvtr >= Case When :ivtrvon <> 0 Then :ivtrvon Else vsvtr end
And vsvtr <= Case When :ivtrbis <> 0 Then :ivtrbis Else vsvtr end
And Case When :iverb <> 0 Then vsvbnr else 9999999 end
in(Select stverb from WUVT0547VB)
And Case When :ikdnr <> 0 Then :ikdnr Else vskdnr end = vskdnr
And Case When :imagr <> ' ' Then :imagr Else vsmagr end
= vsmagr
And Case When :imhptgr <> ' ' Then :imhptgr Else
coalesce(mi1v22, ' ') end = coalesce(mi1v22, ' ')
And Case When :imarkind <> ' ' Then :imarkind Else vsmark end
= vsmark
And Case When :iwg <> ' ' Then :iwg Else vsmrze end = vsmrze
And Case When :iprodhgr <> ' ' Then :iprodhgr Else vsprhg end
= vsprhg
And Case When :iprodgr <> ' ' Then :iprodgr Else vsprgr end
= vsprgr
And Case When :iprodugr <> ' ' Then :iprodugr Else vsprug end
= vsprug
And Case When :igebgr <> ' ' Then :igebgr Else vsgebi end = vsgebi
And Case When :igebart <> ' ' Then :igebart Else vsgeba end
= vsgeba
And Case When :iuland <> ' ' Then :iuland Else
coalesce(mi1v19, ' ') end = coalesce(mi1v19, ' ')
And Case When :ipreisgr <> ' ' Then :ipreisgr Else vspgrg end
= vspgrg
And Case When :itnr <> ' ' Then :itnr Else vstnr end = vstnr
And Case When :istatus <> ' ' Then :istatus Else imsts end
= imsts
Group by
case when :IndKumVb = '1' or :IndKumKd = '1' then 0 else vsman end,
case when :IndKumVb = '1' or :IndKumKd = '1' then vsvbnr else 0 end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana1 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana2 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.acoi else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.apla else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.aort else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.astr else ' ' end,
case when :IndKumKd = '1' then vskdnr else 0 end,
case when :IndKumKd = '1' then kdslst else 0 end,
case when :IndKumKd = '1' then b.ana1 else ' ' end,
case when :IndKumKd = '1' then b.ana2 else ' ' end,
case when :IndKumKd = '1' or :IndKumLd = '1' then b.acoi else ' ' end,
case when :IndKumKd = '1' then b.apla else ' ' end,
case when :IndKumKd = '1' then b.aort else ' ' end,
case when :IndKumKd = '1' then b.astr else ' ' end,
case when :IndKumKd = '1' then vskdgr else ' ' end,
case when :IndKumKd = '1' then c.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslbr else ' ' end,
case when :IndKumKd = '1' then d.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslpt else ' ' end,
case when :IndKumKd = '1' or :IndKumVt = '1' then vsvtr else 0 end,
case when :IndKumKd = '1' or :IndKumVt = '1' then e.tabbez else ' '
end,
case when :IndKumKd = '1' then kdsbdw else ' ' end,
case when :IndKumKd = '1' then kdfu1 else 0 end,
// Teilenr, Bez, Status, Eigen-/Fremdfertigung, Prod.EAN, Karton.EAN, Zolltarif-Nr.
case when :IndKumTn = '1' then vstnr else ' ' end,
case when :IndKumTn = '1' then imdsc else ' ' end,
case when :IndKumTn = '1' then imsts else ' ' end,
case when :IndKumTn = '1' then imcode else ' ' end,
case when :IndKumTn = '1' then imrsv9 else ' ' end,
case when :IndKumTn = '1' then imrsv0 else ' ' end,
case when :IndKumTn = '1' then mi1swn else ' ' end,
// Preisgr, MarkenGruppe
case when :IndKumTn = '1' or :IndKumPr = '1' then vspgrg else ' ' end,
case when :IndKumTn = '1' or :IndKumPr = '1' then f.prbez else ' '
end,
case when :IndKumTn = '1' then vsmagr else ' ' end,
case when :IndKumTn = '1' then g.tabbez else ' ' end,
// MarkenHptGr, MarkeInd, Warengruppe
case when :IndKumTn = '1' then mi1v22 else ' ' end,
case when :IndKumTn = '1' then h.tabbez else ' ' end,
case when :IndKumTn = '1' then vsmark else ' ' end,
case when :IndKumTn = '1' then i.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then vsmrze else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then j.tabbez else ' '
end,
// ProdHptGr, ProdGr 
case when :IndKumTn = '1' then vsprhg else ' ' end,
case when :IndKumTn = '1' then p.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then vsprgr else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then k.tabbez else ' '
end; 

[BenderD]

... das geht wohl nur als dynamic sql, sprich dein resultierendes SQL Statement zusammenbasteln und mit execute gegen die Datenbank fahren.

D*B

[Fuerchau]

Soweit ich weiß, sind in GroupBy keine Hostvariablen möglich.

Wie Dieter schon sagt, den SQL per Statement zusammenbauen und per Prepare vorbereiten.
Der Cursor muss auf "declare cursor for Statement-Name" umgestellt werden, der Fetch kann trotzdem unverändert bleiben.

[Tobse77]

Vielen Dank euch beiden für die Hilfe.

Muss euch jedoch sagen, dass es doch funktioniert.
Mein Fehler lag einzig in einer vertauschten Feldangabe, die der Dateibeschreibung zuwider lief. Konnte mir natürlich niemand im Forum beantworten, da ich das Satzbett nicht veröffentlicht hatte.

Trotzdem vielen Dank.

Bin jedoch mit dem bisher Erreichten noch nicht ganz zufrieden.
Da das Resultat dieser Abfrage dem Anwender direkt in Excel dargestellt wird, würde ich gerne noch eine Sortierung in meiner Insert-Anweisung ergänzen.

Diese muss - natürlich - auch wieder in Abhängigkeit der gewünschten Kumulationskriterien erfolgen.

Folgende Order by-Anweisungen bringen mir jedoch bei der Kompilierung pro Zeile einen Fehler ein:

 
..
 
Order By
case when :IndKumLd = '1' then b.acoi end,
case when :IndKumVt = '1' then vsvtr end,
case when :IndKumVb = '1' or :IndKumKd = '1' then vsvbnr end,
case when :IndKumKd = '1' then vskdnr end,
case when :IndKumWG = '1' then vsmrze end,
case when :IndKumPG = '1' then vsprgr end,
case when :IndKumPr = '1' then vspgrg end,
case when :IndKumTn = '1' then vstnr end; 

Im Fehlerprotokoll heißt es dann:

SQL0122 - Position 59 ACOI oder Ausdruck in Select-Liste nicht gültig. Wertigkeit: 30
usw.

Ein "Pseudo"-else

case when :IndKumLd = '1' then b.acoi else ' ' end, 

habe ich übrigens auch schon versucht, jedoch ohne Erfolg.
Hört spätestens im Order by die Verwendungmöglichkeit von Hostvariablen auf?

[kitvb1]

Versuch es so:

case when :IndKumLd = '1' then b.acoi else NULL end,

Dann wird diese Spalte ignoriert. (ist das was Du wolltest?)

[Tobse77]

Danke für den Tipp, aber NULL anstelle von ' ' hat leider auch nichts geändert.
Habs eben probiert.

Versuch es so:

case when :IndKumLd = '1' then b.acoi else NULL end,

Dann wird diese Spalte ignoriert. (ist das was Du wolltest?)

[Fuerchau]

Möglicherweise.
Allerdings gibst du im OrderBy den Ergebnisnamen und keinen Ausdruck an.

In deinem "Select case " musst du jedem Ausdruck einen Namen verpassen " case ... end as name", den du dann im OrderBy angibst, ein Ausdruck ist nicht erlaubt.

Du siehst, eine "bedingte" Sortierung ist leider nicht möglich.

[Tobse77]

Hallo Fuerchau,

danke für deine Erklärung.
ABER...
jetzt denke ich umso mehr, dass es doch gehen müsste.

Habe nun die entsprechenden Felder, nach denen ich anschließend sortieren lassen könnte mit Namen versehen:

Exec SQL
Insert into VOGIO/WUVT0547
Select
case when :IndKumVb = '1' or :IndKumKd = '1' then vsman else 0 end,
case when :IndKumVb = '1' or :IndKumKd = '1' then vsvbnr else 0 end
Verband,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana1 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana2 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.acoi else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.apla else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.aort else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.astr else ' ' end,
case when :IndKumKd = '1' then vskdnr else 0 end Kunde,
case when :IndKumKd = '1' then kdslst else 0 end,
case when :IndKumKd = '1' then b.ana1 else ' ' end,
case when :IndKumKd = '1' then b.ana2 else ' ' end,
case when :IndKumKd = '1' or :IndKumLd = '1' then b.acoi else ' ' end
KdLand,
case when :IndKumKd = '1' then b.apla else ' ' end,
case when :IndKumKd = '1' then b.aort else ' ' end,
case when :IndKumKd = '1' then b.astr else ' ' end,
case when :IndKumKd = '1' then vskdgr else ' ' end,
case when :IndKumKd = '1' then c.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslbr else ' ' end,
case when :IndKumKd = '1' then d.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslpt else ' ' end,
case when :IndKumKd = '1' or :IndKumVt = '1' then vsvtr else 0 end
Vertreter,
case when :IndKumKd = '1' or :IndKumVt = '1' then e.tabbez else ' '
end,
case when :IndKumKd = '1' then kdsbdw else ' ' end,
case when :IndKumKd = '1' then kdfu1 else 0 end,
// Teilenr, Bez, Status, Eigen-/Fremdfertigung, Prod.EAN, Karton.EAN, Zolltarif-Nr.
case when :IndKumTn = '1' then vstnr else ' ' end Teilenr, 
... 

Wenn ich nun mein Order By-Statement folgendermaßen aufbaue:

Order By
KdLand, Vertreter, Verband, Kunde; 

funktioniert alles bestens, d. h. er sortiert in Reihenfolge der fix angegebenen Namen.

Baue ich aber mein Order By-Statement mit Case-Anweisungen auf:

Order By
case when :IndKumLd = '1' then KdLand end,
case when :IndKumVt = '1' then Vertreter end,
case when :IndKumVb = '1' or :IndKumKd = '1' then Verband end,
case when :IndKumKd = '1' then Kunde end,
case when :IndKumWG = '1' then Warengr end,
case when :IndKumPG = '1' then Produktgr end,
case when :IndKumPr = '1' then Preisgr end,
case when :IndKumTn = '1' then Teilenr end; 

dann bringt mir der Compiler wieder die SQL0122-Fehler.
Setze ich die Namen im Order by in Hochkommas:

Order By
case when :IndKumLd = '1' then 'KdLand' end,
case when :IndKumVt = '1' then 'Vertreter' end, 

wandelt er mir das Programm zwar um, sortiert aber nicht

WIESO??
Ich kapiers einfach nicht!

[kitvb1]

It's too late in the afternoon to translate this into German

Have you maybe read this article? SQL: Made to Order.

This is the Article published in the July 2009 edition iSeries Nation Network newsletter. The original article can be found in the Tekkie-Corner (Chapter 3) at http://www.sss-software.de/inn/power...-PowerInfo.pdf

The above link does not work anymore, but you can still access it here SQL: Made to Order. (with some extra info as well**).

IMO, using too many "case when" statements makes the statement hard to maintain for the next developer (as you have found). In your example I would definitely use traditional dynamic SQL (at the cost of a slight performance hit) and build the statement into a string.

**For example, you can put your sort sequences into a file and simply fetch the sequence needed. This means should you have a new sequence option, you add it to the file and don't need to even change the program anymore.

Hope this helps.

[Tobse77]

Thank you for your article.

I have to admit that it's getting complex, but until today I used different SQL-Statements for each accumulation criterion and therefore only one criteria could be used at the same time.

Since I wanted to offer our users more possibilities it's necessary to have a dynamic statement.
IMO, the current statement is not too complex (furthermore I'm the only programmer in our team who is able to code RPGIV and SQL ).

In the meantime I've helped myself by arranging the "order by" criterias in a common way, which works in 99% of upcoming cases.

Thanks again and have good remainder of the day.

-- Tobias

[Fuerchau]

Für die Sortierung brauchst du Namen und keine Konstanten.

[Tobse77]

Ja... hab' mich nun auch damit abgefunden.
Aber wie oben schon beschrieben, habe ich nun eine passable Lösung für 99% der Fälle. Sollte ein Benutzer die Konstellation für das eine Prozent erwischen, dann muss er sich halt in Excel die Darstellung optimieren

Dankeschön und einen erholsamen Feierabend.

-- Tobias