Doppelte Einträge ausschließen / Case Abfrage

Freshman78 · 2 Juli 2014

Hallo Zusammen,

ich versuche aus einer Sybase SQL Datenbank CSV Listen auszuspielen. Dabei hänge ich immer wieder an einer bestimmten Stelle.

Ich habe eine Tabelle Akte, in der sämtliche Vorgänge (z.B. Kunde wurde angerufen, Kunde hatte Rückfrage usw.) aufgelistet sind. Diese Vorgänge sind mit einem Kürzels in der Tabelle VGVorlage.Kuerzel eingetragen. Ich möchten mir nun alle Kunden die innerhalb eines bestimmten Zeitraums angelegt wurden anzeigen lassen.

WENN in der Akte des Kunden ein Vorgangskürzel "h00" vorhanden ist, dann soll er mir dieses in eine Spalte [Vorgangskürzel] eintragen. Außerdem möchte ich in diesem Fall noch das Belegdatum Vorgang.Belegdatum zu diesem Kürzel in die Spalte [Datum des Kontakts] eingetragen bekommen.

Sollte KEIN Vorgangskürzel "h00" vorhanden sein, muss in die Spalte Vorgangskürzel "Kein H00" eingetragen werden. Das Datumsfeld kann dann leer bleiben.

Hier mein Code an der entsprechenden Stelle:
Case VGVorlage.Kuerzel
WHEN 'h00'
THEN 'H00'
ELSE 'Kein H00'
END as [Vorgangskürzel]

Case VGVorlage.Kuerzel
WHEN 'h00'
THEN Vorgang.Belegdatum
END AS [Datum des Kontakts]

In einer Akte sind diverse Vorgänge. Damit ich keine Mehrfacheinträge für "kein H00" bekomme, habe ich im Selectteil "SELECT Distinct" verwendet. Leider bekomme ich aber bei allen Akten, die einen "H00"-Vorgang beinhalten eine Zeile mit H00 + Belegdatum ausgegeben und danach eine Zeile mit "kein H00"

Am liebsten würde ich beide Case-Anweisungen (Case when ist ja jeweils gleich) in einen Block schreiben, aber wenn ich mehrer Anweisungen in den Then Teil schreibe bekomme ich immer einen Syntax Error.

Hat jemand einen Tipp für mich?

akretschmer · 2 Juli 2014

Freshman78 schrieb:
Am liebsten würde ich beide Case-Anweisungen (Case when ist ja jeweils gleich) in einen Block schreiben, aber wenn ich mehrer Anweisungen in den Then Teil schreibe bekomme ich immer einen Syntax Error.

Logisch, da kannst Du nur einzelne Spalten definieren. Bei mir geht folgendes, sollte aber Standard-SQL sein (ich : PostgreSQL)

Code:

test=*# select * from freshman ;
 id | kuerzel | datum
----+---------+-------
  1 | hoo  |
  2 | foobar  |
(2 rows)

test=*# select *, case when kuerzel='hoo' then 'Hoo' end as vorgang, case when kuerzel = 'hoo' then datum else null end as kontaktdatum from freshman ;
 id | kuerzel | datum | vorgang | kontaktdatum
----+---------+-------+---------+--------------
  1 | hoo  |  | Hoo  |
  2 | foobar  |  |  |
(2 rows)

Falls das nicht paßt dann zeig mal ein Beispiel, wie du es gern hättest, so ganz kann ich Dir grad ned folgen.

Freshman78 · 2 Juli 2014

Hier mal das gesamte Statement:

SELECT Distinct Akte.AZ as [Aktenzeichen],

Case WHEN VGVorlage.Kuerzel = 'h00'
THEN 'H00'
END as [Vorgangl],

Case WHEN VGVorlage.Kuerzel = 'h00'
THEN Vorgang.Belegdatum
ELSE null
END AS [Datum]

FROM akte
JOIN Vorgang on Akte.Akte_ID = Vorgang.Akte_ID
JOIN VGVorlage on Vorgang.VgVorl_ID = VgVorlage.VgVorl_ID

WHERE
(Akte.Uebernahme >= '"+DtoS(SQLDatumAb)+"' and Akte.Uebernahme <= '"+DtoS(SQLDatumBis)+"' )

-------------------------------

Die Tabelle die Ausgegeben wird sieh dann etwa so aus:

Aktenzeichen / Vorgang / Datum
10001 / kein H00 / . .
10002 / kein H00 / . .
10003 / H00 / 01.01.2014
10003 / kein H00 / . .

Ich bekomme also bei allen Akten in den ein ein Vorgang H00 ist auch nochmal eine extra Zeile "kein H00". In diesen Akten gibt es selbstverständlich auch weitere Vorgänge, nur möchte ich diese halt im Falle eines bestehenden Vorgangs H00 nicht angezeigt bekommen, sonst aber schon --> siehe Aktenzeichen 10003

akretschmer · 2 Juli 2014

Freshman78 schrieb:
Ich bekomme also bei allen Akten in den ein ein Vorgang H00 ist auch nochmal eine extra Zeile "kein H00". In diesen Akten gibt es selbstverständlich auch weitere Vorgänge, nur möchte ich diese halt im Falle eines bestehenden Vorgangs H00 nicht angezeigt bekommen, sonst aber schon --> siehe Aktenzeichen 10003

Ich tippe auf Dein Join, der liefert da schon die Rows.

ukulele · 2 Juli 2014

Ich denke du suchst so etwas:

Code:

SELECT Distinct Akte.AZ as [Aktenzeichen],

Case WHEN VGVorlage.Kuerzel = 'h00'
THEN 'H00'
END as [Vorgangl],

Case WHEN VGVorlage.Kuerzel = 'h00'
THEN Vorgang.Belegdatum
ELSE null
END AS [Datum]

FROM akte
JOIN Vorgang on Akte.Akte_ID = Vorgang.Akte_ID
JOIN VGVorlage on Vorgang.VgVorl_ID = VgVorlage.VgVorl_ID

WHERE
(Akte.Uebernahme >= '"+DtoS(SQLDatumAb)+"' and Akte.Uebernahme <= '"+DtoS(SQLDatumBis)+"' )

AND    (    VGVorlage.Kuerzel = 'h00'
OR NOT EXISTS (    SELECT    1
                FROM    VGVorlage
                WHERE    VgVorl_ID = Vorgang.VgVorl_ID
                AND        Kuerzel = 'h00' ) )

Bin mir aber mit dem Subselect in NOT EXISTS nicht ganz sicher. Testen.

Freshman78 · 2 Juli 2014

Hallo Ukulele,

das statement wird ohne Fehlermeldung ausgespuckt, aber das Einzige was sich verändert hat, ist die Sortierreihenfolge. Erst bekomme ich alle Akten mit H00 angezeigt, danach alle anderen. Sortiere ich dann nach Aktenzeichen um, finden sich wieder die gleichen doppeleinträge wie zuvor. Mist!, dachte schon es würde laufen.

akretschmer · 2 Juli 2014

Freshman78 schrieb:
Hallo Ukulele,

das statement wird ohne Fehlermeldung ausgespuckt, aber das Einzige was sich verändert hat, ist die Sortierreihenfolge. Erst bekomme ich alle Akten mit H00 angezeigt, danach alle anderen. Sortiere ich dann nach Aktenzeichen um, finden sich wieder die gleichen doppeleinträge wie zuvor. Mist!, dachte schon es würde laufen.

Siehe meine erste Antwort. Du gehst schon mir mehreren Zeilen in den Join, wenn ich dich richtig verstehe. Also du hast:

Code:

test=# select * from freshman ;
 id | kuerzel | datum
----+---------+-------
  1 | hoo  |
  2 | foobar  |
  1 | blub  |
  1 |  |

Du willst aber je id nur eine Zeile, wenn kuerzel='hoo' existiert dann diese. Lösung:

Code:

test=*# select * from (select *, row_number() over (partition by id order by case when kuerzel='hoo' then 0 else 1 end, kuerzel) from freshman) foo where row_number=1 ;
 id | kuerzel | datum | row_number
----+---------+-------+------------
  1 | hoo  |  |  1
  2 | foobar  |  |  1
(2 rows)

Das ist jetzt PostgreSQL, Sybase kann sicherlich auch row_number() oder was ähnliches in der Art.

Freshman78 · 2 Juli 2014

Sorry, aber ich habe leider ewig nix mit SQL gemacht und verstehe dein Statement nicht ganz, bzw. kann es irgendwie nicht auf meine Datenbankbezeichnungen umändern.

Meinst du in etwa das:
FROM
(SELECT VGVorlage.Kuerzel, rownumer() over (partition by id order by case when Vorgang.Kuerzel = 'H00' then 0 else 1 end)
FROM VGVorlage)
foo WHERE row_number=1;

Was ist foo für ein Platzhalter? Hilf mir vom Schlauch runter

akretschmer · 2 Juli 2014

Freshman78 schrieb:
Sorry, aber ich habe leider ewig nix mit SQL gemacht und verstehe dein Statement nicht ganz, bzw. kann es irgendwie nicht auf meine Datenbankbezeichnungen umändern.

Meinst du in etwa das:
FROM
(SELECT VGVorlage.Kuerzel, rownumer() over (partition by id order by case when Vorgang.Kuerzel = 'H00' then 0 else 1 end)
FROM VGVorlage)
foo WHERE row_number=1;

Was ist foo für ein Platzhalter? Hilf mir vom Schlauch runter

foo ist nur ein Alias für die im Subselect erstellte Tabelle. Ohne dem passiert dies:

Code:

test=*# select * from (select *, row_number() over (partition by id order by case when kuerzel='hoo' then 0 else 1 end, kuerzel) from freshman)  where row_number=1 ;
ERROR:  subquery in FROM must have an alias at character 15
HINT:  For example, FROM (SELECT ...) [AS] foo.
STATEMENT:  select * from (select *, row_number() over (partition by id order by case when kuerzel='hoo' then 0 else 1 end, kuerzel) from freshman)  where row_number=1 ;
ERROR:  subquery in FROM must have an alias
LINE 1: select * from (select *, row_number() over (partition by id ...
  ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

ukulele · 2 Juli 2014

Also ich würde CASE auf jedenfall raus schmeißen und mit LEFT JOIN arbeiten, dabei bekommst du automatisch NULL Werte für Kürzel und Datum. Schwierig sind die Tabellen Vorgang und VGVorlage. Ich vermute die liefern mehr als eine Zeile zu jeder Akte_ID, das kann ich aber nicht wissen weil ich deine Daten nicht kenne.

Wenn also das hier nicht schon funktionieren sollte:

Code:

SELECT   Akte.AZ AS [Aktenzeichen],
     t.Kuerzel AS [Vorgang],
     t.Belegdatum AS [Datum]
FROM   akte
LEFT JOIN (

SELECT   Vorgang.Akte_ID,
     Vorgang.Belegdatum,
     VGVorlage.Kuerzel
FROM   Vorgang,
     VGVorlage
WHERE   Vorgang.VgVorl_ID = VgVorlage.VgVorl_ID

) t ON Akte.Akte_ID = t.Akte_ID
WHERE   Akte.Uebernahme >= '"+DtoS(SQLDatumAb)+"'
AND     Akte.Uebernahme <= '"+DtoS(SQLDatumBis)+"'

dann muss es in etwa so gehen:

Code:

SELECT   Akte.AZ AS [Aktenzeichen],
     t.Kuerzel AS [Vorgang],
     t.Belegdatum AS [Datum]
FROM   akte
LEFT JOIN (

SELECT   Vorgang.Akte_ID,
     max(Vorgang.Belegdatum) AS Belegdatum,
     VGVorlage.Kuerzel
FROM   Vorgang,
     VGVorlage
WHERE   Vorgang.VgVorl_ID = VgVorlage.VgVorl_ID
GROUP BY Vorgang.Akte_ID,VGVorlage.Kuerzel

) t ON Akte.Akte_ID = t.Akte_ID
WHERE   Akte.Uebernahme >= '"+DtoS(SQLDatumAb)+"'
AND     Akte.Uebernahme <= '"+DtoS(SQLDatumBis)+"'

Freshman78 · 2 Juli 2014

Ich hatte doch geschrieben, dass in einer Akte diverse Vorgänge vorhanden sein können. Ich hab jetzt mal eine Zeichnung der genauen Tabellenstruktur gemacht:

Die eigentliche Abfrage ist also noch etwas umfangreicher...

Ich teste aber jetzt erstmal deinen Vorschlag.

ukulele · 3 Juli 2014

Mit GROUP BY und max() versuche ich ja den neusten Beleg zu holen. Ich denke aber mal es gibt verschiedene Kürzel mit Belegdatum, die müsste man noch ausschließen.

Freshman78 · 3 Juli 2014

Hab mir erstmal Interactive SQL besorgt, damit ich den SQL Code direkt auf die Datenbank anweden kann, ohne über die Software zu gehen.

Das Ergebnis deines Vorschlags zeigt mir allerdings lediglich 8 Ergebniszeilen für die 3 angefragten Akten weniger an. (62 anstatt 70)

Code:

SELECT DISTINCT
Akte.MandNr as [Mandant],
Akte.Az as [Aktenzeichen],
Akte.Stadium as [Stadium],
Kontakt.Name1 as [Nachname],
Kontakt.Vorname as [Vorname],
Mandant.KundeSeit as [Kunde seit],
Mandant.Verkaeufer as [Verkäufer],
EPVertrag.Bezeichnung as [EP-Vertrag],
t.Kuerzel as [Vorgang],
t.Belegdatum as [Datum]

FROM
Akte
  JOIN Kontakt
   ON Akte.Mandant_ID = Kontakt.Kontakt_ID
  JOIN Mandant
   ON Akte.Mandant_ID = Mandant.Kontakt_ID
  JOIN EPVertrag
   ON Mandant.EPVertrag_ID = EPVertrag.EPVertrag_ID
  LEFT JOIN (
   SELECT
    Vorgang.Akte_ID,
    max(Vorgang.Belegdatum) as Belegdatum,
    VGVorlage.Kuerzel
   FROM
    Vorgang,
    VGVorlage
   WHERE
    Vorgang.VgVorl_ID = VgVorlage.VgVorl_ID
   GROUP BY Vorgang.Akte_ID, VGVorlage.Kuerzel)
   t on Akte.Akte_ID = t.Akte_ID
   WHERE
  Akte.MandNr = '606'
   AND
    (Akte.Uebernahme >= '2000-01-01' and Akte.Uebernahme <= '2014-07-03' )

Bei meiner vorherigen Abfrage waren es wie gesagt noch 70 Ergebniszeilen:

Code:

SELECT DISTINCT
Akte.MandNr,
Akte.Az,
Akte.Stadium,
Kontakt.Name1,
Kontakt.Vorname,
Mandant.KundeSeit,
Mandant.Verkaeufer,
EPVertrag.Bezeichnung,
VGVorlage.Kuerzel,
Vorgang.Belegdatum
FROM
Akte
  JOIN Kontakt
   ON Akte.Mandant_ID = Kontakt.Kontakt_ID
  JOIN Mandant
   ON Akte.Mandant_ID = Mandant.Kontakt_ID
  JOIN EPVertrag
   ON Mandant.EPVertrag_ID = EPVertrag.EPVertrag_ID
  JOIN Vorgang
   ON Akte.Mandant_ID = Vorgang.Akte_ID
  JOIN VGVorlage
   ON Vorgang.VgVorl_ID = VGVorlage.VgVorl_ID
WHERE
  Akte.MandNr = '606'
   AND
    (Akte.Uebernahme >= '2000-01-01' and Akte.Uebernahme <= '2014-07-03' )

Hier ein kurzer Screenshot wie die Ausgabe deines Vorschlags aussieht. Das Ergebnis habe ich aber nachträglich nach Akenzeichen geordnet um besser sehen zu können, welche Zeilen pro Akte entstehen:

https://www.dropbox.com/s/wngn1w1gd7sycdy/Ausgabe.JPG

ukulele · 3 Juli 2014

Wie ich schon vermutet hatte gibt es mehr als ein Kürzel. Wenn die anderen Kürzel und deren Datum jetzt egal sind (also nicht in der Ausgabe stehen sollen) könnte man das mit einer Zeile mehr leicht filtern:

Code:

SELECT    Akte.AZ AS [Aktenzeichen],
        t.Kuerzel AS [Vorgang],
        t.Belegdatum AS [Datum]
FROM    akte
LEFT JOIN (

SELECT    Vorgang.Akte_ID,
        max(Vorgang.Belegdatum) AS Belegdatum,
        VGVorlage.Kuerzel
FROM    Vorgang,
        VGVorlage
WHERE    Vorgang.VgVorl_ID = VgVorlage.VgVorl_ID
AND        VGVorlage.Kuerzel = 'H00'
GROUP BY Vorgang.Akte_ID,VGVorlage.Kuerzel

) t ON Akte.Akte_ID = t.Akte_ID
WHERE    Akte.Uebernahme >= '"+DtoS(SQLDatumAb)+"'
AND        Akte.Uebernahme <= '"+DtoS(SQLDatumBis)+"'

Freshman78 · 3 Juli 2014

Leider benötige ich für eine Akte MIT bestehenden H00 die Zeile des H00 Vorgangs (und wirklich nur exakt diese).
Für Akten Ohne H00 benötige ich eine Zeile, in der alle anderen Angaben (Aktenzeichen, Stdium, ect.) stehen außer das Vorgangskürzel und das Belegdatum. Diese beiden Spalten sollten dann am besten leer bleiben. Bei den von mir abgefragten 3 Akten müssen also genau 3 Ergebniszeilen rauskommen.

Kann man das eventuell irgendwie über EXISTS im SELECT lösen?

Also sowas ähnliches (so hab ich es schon getestet und es geht natürlich nicht) wie:
SELECT
....
Case
When Exists (t.Kuerzel = 'h00')
THEN t.Kuerzel
END as [Kürzel]
ELSE
THEN NULL
END as [Kürzel]

Doppelte Einträge ausschließen / Case Abfrage

Freshman78

Benutzer

akretschmer

Datenbank-Guru

Freshman78

Benutzer

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru

Freshman78

Benutzer

akretschmer

Datenbank-Guru

Freshman78

Benutzer

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru

Freshman78

Benutzer

Anhänge

ukulele

Datenbank-Guru

Freshman78

Benutzer

ukulele

Datenbank-Guru

Freshman78

Benutzer

Ähnliche Themen