Gruppierung in Abfrage einbauen

[FrenchSpirit]

Hallo in die Runde,

ich habe leider noch eine Anfängerfrage in Sachen SQL.

Folgende Auswertung möchte ich machen:

Select
KTO.KONTONR,
KTO.PERSONEN_NR,
PE.PERS_ART,
PE.NAME,
from
z_konten KTO
inner join
z_Personen PE
on KTO.PERSONEN_NR = PE.PERSONEN_NR
where
KTO.AUFLOESUNGSDATUM like '31.12.99'


Hier werden mir alle Konten gelistet.


Ich kann mit der zweiten Abfrage :

Select
KTO.PERSONEN_NR
, COUNT (*) AS ANZAHL
from
z_konten KTO
where
KTO.AUFLOESUNGSDATUM like '31.12.99'
Group by KTO.PERSONEN_NR
HAVING COUNT(*) > 1

alle Personen ermitteln, die mehr als ein Konto haben.

Nun möchte ich aber von diesen Personen die restlichen Datenfelder aus dem Selectbereich der ersten Abfrage haben.

Wie kann ich beide Abfragen kombinieren?

Ich habe schon versucht, die Abfrage 2 als zweite Tabelle in den INNER JOIN zu nehmen, bin aber gescheitert....


Vielen Dank vorab für Eure Hilfe

Gruß

Andreas

 

[akretschmer]

einfachster Weg: du erweiters Deine erste Abfrage um diese Where-Condition:

... and KTO.PERSONEN_NR in ( hier deine 2. Abfrage ). Ungetestet.

Du könntest auch Deine erste Abfrage um ein row_number() over (partition by kto.personen_nr) - Konstrukt erweitern und alle mit row_number >= 2 filtern. So als Idee...

 

[drdimitri]

Moin,

unabhängig von der Aufgabenstellung fällt mir deine Datumsverarbeitung ins Auge:

KTO.AUFLOESUNGSDATUM like '31.12.99'

a) Bist Du abhängig von den Ländereinstellungen im Client. Auf einem Gerät mit anderen Einstellungen wird die Abfrage nicht mehr laufen.
b) Abfragen mit LIKE ohne Wildcard in ein = umwandeln.
c) Datumsbereiche nicht mit LIKE abfragen, da Oracle hier keine Datumsarithmetik verwendet, sondern das Datum vorher in einen String umwandelt und dann einen Stringvergleich durchführt. Ein ggf. vorhandener Index kann hier nicht verwendet werden.

Also

KTO.AUFLOESUNGSDATUM = to_date('31.12.9999','DD.MM.YYYY')

Das andere wird einfach per Subselect gelößt:

SELECT
  kto.kontonr,
  kto.personen_nr,
  pe.pers_art,
  pe.name,
FROM
  z_konten kto
  INNER JOIN z_personen pe ON kto.personen_nr = pe.personen_nr
  INNER JOIN(SELECT personen_nr ,COUNT (*) AS ANZAHL
               FROM z_konten
               WHERE aufloesungsdatum = to_date('31.12.9999','DD.MM.YYYY')
               GROUP BY personen_nr
               HAVING COUNT(*) > 1) ) konto2 ON konto2.personen_nr=kto.personen_nr)

Du könntest auch Deine erste Abfrage um ein row_number() over (partition by kto.personen_nr) - Konstrukt erweitern und alle mit row_number >= 2 filtern. So als Idee...

Wenn z_konten und z_personen nicht 1:1 sind wird das nicht wie gewünscht klappen.

 

[FrenchSpirit]

Hallo ihr beiden,
ich bin immer wieder begeistert, wie schnell man in einem Spezialistenforum kompetente Hilfe bekommt. Vielen Dank dafür!

Der folgende Selekt ffunktioniert:

Select
KTO.KONTONR,
KTO.GP_KEY,
KTO.GP_VARIANTE,
KTO.PERSONEN_NR,
KTO.EROEFFNUNGSDATUM,
PE.PERS_ART,
PE.NAME,
PE.VORNAME,
PE.OE_SCHL

from
z_konten KTO

inner join z_Personen PE on KTO.PERSONEN_NR = PE.PERSONEN_NR
INNER JOIN(SELECT personen_nr ,COUNT (*) AS ANZAHL
FROM z_konten
WHERE aufloesungsdatum = '31.12.9999'
AND GP_VARIANTE like '%NETD%'
GROUP BY personen_nr
HAVING COUNT(*) > 1) konto2 ON konto2.personen_nr=kto.personen_nr
where
KTO.GP_VARIANTE like '%NETD%'
AND KTO.AUFLOESUNGSDATUM = '31.12.9999'


Allerdings bekomme ich von Oracle noch den Hinweis: "ORA-00920: Ungültiger relationaler Operator". Dies ist mir unklar.

Es werden alle Konten angezeigt, ich würde nun gerne noch die gezählte Anzahl der Konten pro Kontoinhaberperson mit in den Selectbereich für die Ausgabe hinzufügen.

Wie stelle ich das an?

Grüße

Andreas

 

[FrenchSpirit]

Ich erlaube mir noch eine ergänzende Frage:

Was ist der Unterschied zwischen:

a) KTO.AUFLOESUNGSDATUM = '31.12.9999'

und

b) KTO.AUFLOESUNGSDATUM = to_date('31.12.9999','DD.MM.YYYY')

Das habe ich noch nicht verstanden. Im Selekt bekomme ich in beiden Fällen die selben Ergebnisse.

Gruß

Andreas

 

[drdimitri]

Mit to_date gibst du einen Formatierungsstring mit und legst selbst fest, wie dein Datum formatiert ist.
Ohne to_date verwendet die Datenbank die Ländereinstellungen deines Clients und macht das impliziert für dich. Das bedeutet natürlich auch , dass auf einem Rechner mit anderen Ländereinstellungen als Deutsch dieses Statement plötzlich Fehler verursacht.
Daher ist die Angabe von to_date quasi verpflichtend.

Um die Anzahl anzuzeigen, nimmst du das Feld einfach in deine Select Liste auf. Genau so, wie wenn du ein echtes Feld einer Tabelle nehmen würdest. Also konto2.anzahl

 

[FrenchSpirit]

Hallo DrDimitri,

bestens. Vielen Dank. Ich hatte versucht, den COUNT-Befehl (also Count(Kontonr)) direkt in den select-Bereich aufzunehmen. Mit Deiner Hilfe hat es wunderbar geklappt.

Bzgl. des Datumsformats werde ich es es auch mit to_date übernehmen, man weiss ja nicht, ob ich mit dem Client mal woanders sitzen werden )

Viele Grüße und ein schönes Wochenende

Andreas