Select-Resultat in einer Spalte anzeigen

[Perlsau]

In einer Tabelle ZUSATZ verwalte ich Lebensmittel-Zusatzstoffe. Diese Zusatzstoffe gehören teilweise zu mehreren Klassen, die ich in der Tabelle ZUSATZKLASSEN verwalte. Z.B. ist Schwefeldioxyd einerseits ein Antioxidationsmittel, aber auch ein Konservierungsstoff. Diese Relationen verwalte ich in der Tabelle ZUSATZ_KLASSEN: für jede Kombination Zusatzstoff-Zusatzklasse existiert ein Eintrag, für Schwefeldioxyd mit der Id 1 also zwei Einträge.

Besteht nun die Möglichkeit, in einem Firebird-View eine Spalte einzurichten, die alle Einträge eines Select-Ergebnisses enthält, durch Komma getrennt? Genauer: Ich möchte in einem View der Tabelle ZUSATZ eine Spalte haben, in der alle zum jeweiligen Eintrag gehörende Zusatzklassen durch Komma getrennt angezeigt werden. Ich habe hier dieses View:

Code:

CREATE OR ALTER VIEW V_ZUSATZ(
    Z_ID,
    Z_NAME,
    Z_ALT,
    Z_KLASS,
    Z_ENUM,
    Z_NOTIZ,
    Z_BIO,
    Z_GVO,
    Z_MARK)
AS
select
ZUSATZ.ID_ZUSATZ,
ZUSATZ.Z_NAME,
ZUSATZ.ALTNAME,

ZUSATZ.ENUMMER,
ZUSATZ.Z_NOTIZ,
ZUSATZ.BIO,
ZUSATZ.GVO,
ZUSATZ.MARKIERT

from ZUSATZ;

Dazu soll im Feld Z_KLASS das Ergebnis des folgenden Selects (eines weiteren Views) angezeigt werden, und zwar alle gefundenen Einträge durch Komma getrennt:

Code:

select ZK_KLASSE from V_ZUSATZ_KLASSE where ZK_ZUSATZ = Z_ID (bzw. ZUSATZ.ID_ZUSATZ)

Das View V_ZUSATZ_KLASSE enthält neben der ID für den Zusatzstoff die Klasse in Textform:

Code:

CREATE OR ALTER VIEW V_ZUSATZ_KLASSE(
    ZK_ZUSATZ,
    ZK_KLASSE)
AS
select
ZUSATZ_KLASSE.ZUSATZ,
ZUSATZKLASSE.KLASSE

from ZUSATZ_KLASSE

inner join ZUSATZKLASSE on ZUSATZKLASSE.ID_ZUSATZKLASSE = ZUSATZ_KLASSE.KLASSE
 order by ZUSATZ_KLASSE.ZUSATZ, ZUSATZKLASSE.KLASSE;

Wenn ich jetzt von diesem View ein select ZK_KLASSE from V_ZUSATZ_KLASSE where ZK_ZUSATZ = 1 ausführe, dann erhalte ich zwei Einträge:
Antioxidationsmittel
Konservierungsstoff

Diese beiden Einträge sollen also in die Spalte Z_KLASS des Views V_ZUSATZ eingetragen werden, was dann so aussehen sollte:
Antioxidationsmittel, Konservierungsstoff

[DeddyH]

Versuch es mal mit LIST

[Perlsau]

Hi Detlef,

der Hinweis auf LIST war teilweise zielführend: zumindest funktioniert dieser Select-Befehl im IbExpert-SQL-Editor korrekt und zeigt mir drei Zusatzstoff-Klassen hintereinander mit Komma getrennt an:

Code:

select List(ZK_KLASSE,', ') from V_ZUSATZ_KLASSE where ZK_ZUSATZ = 9

Jetzt hab ich – bislang erfolglos – versucht, das in mein View einzubauen:

Code:

CREATE OR ALTER VIEW V_ZUSATZ(
    Z_ID,
    Z_NAME,
    Z_ALT,
    Z_KLASS,
    Z_ENUM,
    Z_NOTIZ,
    Z_BIO,
    Z_GVO,
    Z_MARK)
AS
select
Z.ID_ZUSATZ,
Z.Z_NAME,
Z.ALTNAME,
List(K.ZK_KLASSE,', ') as V_ZUSATZ_KLASSE,
Z.ENUMMER,
Z.Z_NOTIZ,
Z.BIO,
Z.GVO,
Z.MARKIERT

from ZUSATZ Z
inner join V_ZUSATZ_KLASSE K on K.ZK_ZUSATZ = Z.ID_ZUSATZ
group by Z.ID_ZUSATZ;

Dabei erhalte ich diese Fehlermeldung:

SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

[mkinzler]

SQL-Code:

CREATE OR ALTER VIEW V_ZUSATZ(
    Z_ID,
    Z_NAME,
    Z_ALT,
    Z_KLASS,
    Z_ENUM,
    Z_NOTIZ,
    Z_BIO,
    Z_GVO,
    Z_MARK)
AS
select
    Z.ID_ZUSATZ,
    Z.Z_NAME,
    Z.ALTNAME,
    K.V_ZUSATZ_KLASSE,
    Z.ENUMMER,
    Z.Z_NOTIZ,
    Z.BIO,
    Z.GVO,
    Z.MARKIERT
from 
    ZUSATZ Z
        inner join ( 
                    select 
                        List(ZK_KLASSE,', ') as V_ZUSATZ_KLASSE 
                    from 
                        V_ZUSATZ_KLASSE 
                    group by 
                        ID_ZUSATZ ) k on K.ZK_ZUSATZ = Z.ID_ZUSATZ;

[Perlsau]

Danke, Markus, jetzt bin ich wieder einen Schritt weiter ... ... aber leider noch nicht am Ziel. Abgesehen von ein paar Tippfehlern, die ich leicht aufstöbern konnte, bringt deine Version folgende Fehlermeldung:

SQL error code = -206. Column unknown. ID_ZUSATZ. At line 30, column 9.

Das ist der Eintrag nach dem group by und vor der abschließenden Klammer. Nun hab ich mir gedacht, klar, es geht ja um die Tabelle V_ZUSATZ_KLASSE, und die hat keine Spalte mit dem Namen ID_ZUSATZ. Und eigentlich soll das Ganze ja nach der Spalte ZK_ZUSATZ gruppiert sein, also schnell umgeschrieben:

Code:

CREATE OR ALTER VIEW V_ZUSATZ1(
    Z_ID,
    Z_NAME,
    Z_ALT,
    Z_KLASS,
    Z_ENUM,
    Z_NOTIZ,
    Z_BIO,
    Z_GVO,
    Z_MARK)
AS
select
Z.ID_ZUSATZ,
Z.Z_NAME,
Z.ALTNAME,
K.V_ZUSATZ_KLASSE,
Z.ENUMMER,
Z.Z_NOTIZ,
Z.BIO,
Z.GVO,
Z.MARKIERT

from ZUSATZ Z
inner join
   (select
        List(ZK_KLASSE,', ') as V_ZUSATZ_KLASSE
    from
        V_ZUSATZ_KLASSE
    group by
        ZK_ZUSATZ)
        K on K.ZK_ZUSATZ = Z.ID_ZUSATZ;

Das bringt mir folgende Fehlermeldung ein:

SQL error code = -206. Column unknown. K.ZK_ZUSATZ. At line 31, column 16

Das betrifft jetzt also die letzte Zeile. Seltsamerweise existiert die Spalte ZK_ZUSATZ in der Tabelle (dem View) V_ZUSATZ_KLASSE ... Kann es sein, daß die Variable K, die ja die Tabelle V_ZUSATZ_KLASSE repräsentiert, zu diesem Zeitpunkt nur die Spalte ZK_KLASSE kennt?

[mkinzler]

Die Spalte fehlt in der DERIVED TABLE

SQL-Code:

...
K.V_ZUSATZ_KLASSE,
K.ZK_ZUSATZ,
Z.ENUMMER,
Z.Z_NOTIZ,
Z.BIO,
Z.GVO,
Z.MARKIERT

from ZUSATZ Z
inner join
   (select
        ZK_ZUSATZ, List(ZK_KLASSE,', ') as V_ZUSATZ_KLASSE ...

[Perlsau]

Richtig, die Spalte ZK_KLASSE fehlt, das ist der Knackpunkt. Nach einigem Herumprobieren hab ich nun folgende Lösung gefunden:

Code:

CREATE OR ALTER VIEW V_ZUSATZ1(
    Z_ID,
    Z_NAME,
    Z_ALT,
    Z_KLASS,
    Z_ENUM,
    Z_NOTIZ,
    Z_BIO,
    Z_GVO,
    Z_MARK)
AS
select
Z.ID_ZUSATZ,
Z.Z_NAME,
Z.ALTNAME,
K.V_ZUSATZ_KLASSE,
Z.ENUMMER,
Z.Z_NOTIZ,
Z.BIO,
Z.GVO,
Z.MARKIERT

from ZUSATZ Z
inner join
   (select
        List(ZK_KLASSE,', ') as V_ZUSATZ_KLASSE,
        ZK_ZUSATZ as I
    from
        V_ZUSATZ_KLASSE
    group by
        I)
        K on I = Z.ID_ZUSATZ;

Jetzt gibt es nur noch einen kleinen Schönheitsfehler: Die Klassen in der Spalte Z_KLASS sind nicht alphabetisch geordnet. Wo muß ich denn jetzt das Order By ZK_KLASS einfügen?

[mkinzler]

In der DERIVED TABLE

[Perlsau]

Ich glaub, eine sortierte Ausgabe in LIST geht gar nicht:

The ordering of the list values is undefined.

[Dejan Vu]

Schreib dir die Routine doch selbst:

Code:

SET TERM !!;
CREATE PROCEDURE PSLIST(ID INTEGER)
  RETURNS (CONCAT VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(255);
BEGIN
  CONCAT = '';
  FOR SELECT ZK_KLASSE 
        FROM V_ZUSATZ_KLASSE 
       WHERE ID_ZUSATZ = :ID 
       ORDER BY ZK_KLASSE 
  INTO :name 
  DO BEGIN
    CONCAT = CONCAT || coalesce(name,'');
  END
  SUSPEND;
END!!
SET TERM ;!!

Getippt und nicht getestet.