Case When

[Neshi]

Hallo Zusammen;

ich habe folgendes Select erstellt:

Select
x.kd_nr,
x.name,
x.vorname,
(select distinct listagg (t.maildate|| ': ' || r.company_name ||'; ') within group (order by T.maildate) from company r
  join timeline t on (r.company_id = t.adressid)
  join timeline_sent ts on (t.timeline_id = ts.timeline_id)
  where x.kd_nr = t.kd_nr)MAIL_STATUS
from Kunde X
Where x.bestelldatum > ‘20160101’;

Passt alles so weit. Allerdings möchte ich in den Subselect ein Case When einfügen. Und zwar soll er mir als Mail_Status den Listagg ausgeben, wenn das t.maildate befüllt ist. Wenn t.maildate leer ist dann möchte ich den Eintrag '"no E-Mail correspondence" ausgeben lassen.

Select
x.kd_nr,
x.name,
x.vorname,
(select distinct case when t.mailingdate is null then "NO E-MAIL CORRESPONDENCE" else listagg (t.maildate|| ': ' || r.company_name ||'; ') within group (order by T.maildate) from company r
  join timeline t on (r.company_id = t.adressid)
  join timeline_sent ts on (t.timeline_id = ts.timeline_id)
  where x.kd_nr = t.kd_nr)MAIL_STATUS
from Kunde X
Where x.bestelldatum > ‘20160101’;

funktioniert nicht. Kann mir jemand helfen? Vielen Dank schon mal!

[Wernfried]

Vielleicht so?

[HIGHLIGHT=sql]
with t as
(select distinct listagg (t.maildate|| ': ' || r.company_name ||'; ') within group (order by T.maildate) as MAIL_STATUS, kd_nr
from company r
join timeline t on (r.company_id = t.adressid)
join timeline_sent ts on (t.timeline_id = ts.timeline_id) )
Select
x.kd_nr,
x.name,
x.vorname,
NVL(t.MAIL_STATUS, 'no E-Mail correspondence') as MAIL_STATUS
from Kunde X
join t on x.kd_nr = t.kd_nr
Where x.bestelldatum > ‘20160101’;
[/HIGHLIGHT]

Gruss

[Neshi]

Nein, das wird so nicht funktionieren, weil ich in der Tabelle Company keine kd_nr habe! Wenn ich aus der Tabelle timeline t die kd_nr ausgeben lasse, bekomme ich den Fehler "keine Gruppenfunktion für Einzelgruppe"

WITH t AS 
(SELECT DISTINCT listagg (t.maildate|| ': ' || r.company_name ||'; ') WITHIN GROUP (ORDER BY T.maildate) AS MAIL_STATUS, t.kd_nr
  FROM company r
  JOIN timeline t ON (r.company_id = t.adressid)
  JOIN timeline_sent ts ON (t.timeline_id = ts.timeline_id) )
SELECT
x.kd_nr,
x.name,
x.vorname,
NVL(t.MAIL_STATUS, 'no E-Mail correspondence') AS MAIL_STATUS
FROM Kunde X
   JOIN t ON x.kd_nr = t.kd_nr
WHERE x.bestelldatum > ‘20160101’;

[defo]

Nein, das wird so nicht funktionieren,

Dann musst du nicht auf NVL prüfen, sondern auf den Ausdruck, der das Ergebnis eines leeren Listagg ist und dann Deinen Ersatztext hinterlegen oder die Prüfung schon im Subselect vornehmen und ggF. nach dem Prüfkriteriem gruppieren.

also zb. statt NVL(...
decode(MAILSTATUS, ': ' /*1*/, 'keine Email', MAILSTATUS)

1) Oder was sonst bei dem Ausdruck rauskommt, wenn er leer ist. Sollte halt zuverlässig passen.

[Neshi]

Danke Defo! Kannst du mir zeigen wie ich das einbinde?

[defo]

na du tauscht den Abschnitt "nvl(..) as MAILSTATUS" gegen meinen Vorschlag "decode(MAILSTATUS, ': ', 'keine Email', MAILSTATUS) as MAILSTATUS" aus.
Der einzige Unterschied von NVL und DECODE ist, dass erstes gegen NULL prüft und bei True einen Ersatzwert ausspukt, während decode gegen (beliebige) konkrete Werte prüft und bei True Ersatzwerte ausgibt.

[Neshi]

Funktioniert auch nicht!
Ich bekomme diesen Fehler:
ORA-00937: keine Gruppenfunktion für Einzelgruppe
Fehler in Zeile 2 Spalte 134

WITH t AS 
(SELECT DISTINCT listagg (t.maildate|| ': ' || r.company_name ||'; ') WITHIN GROUP (ORDER BY T.maildate) AS MAIL_STATUS, t.kd_nr
  FROM company r
  JOIN timeline t ON (r.company_id = t.adressid)
  JOIN timeline_sent ts ON (t.timeline_id = ts.timeline_id) )
SELECT
x.kd_nr,
x.name,
x.vorname,
decode(t.MAIL_STATUS,': ', 'no E-Mail correspondence', t.MAIL_STATUS) AS MAIL_STATUS
FROM Kunde X
   JOIN t ON x.kd_nr = t.kd_nr
WHERE x.bestelldatum > ‘20160101’;

[Neshi]

Geht das nicht mit einer einfachen Funktion?
Und warum funktioniert das Case When nicht? Danke für eure Hilfe!!!

[defo]

Geht das nicht mit einer einfachen Funktion?
Und warum funktioniert das Case When nicht? Danke für eure Hilfe!!!

decode ist eine einfache Funktion.
Die Gruppierung funktioniert offenbar nicht, mir ist allerdings nicht genau klar, warum.
Wahrscheinlich musst Du extra nach maildate gruppieren.
Da die sich mir aber die gesamte Expression nicht richtig erschließt, die rauskommen könnte, kann ich das nicht sicher sagen.
Wieviel Emailadressen gibt es pro Ergebnissatz?

nebenbei
Case When und decode sind recht ähnlich.

[Neshi]

Ganz unterschiedlich! Zwischen 0 und 20....

[Neshi]

Ich habe noch folgende versucht:

Select
x.kd_nr,
x.name,
x.vorname,
(select distinct
case when t.maildate is null then 'no E-Mail correspondence'
when t.maildate is not null then
(listagg (t.maildate|| ': ' || r.company_name ||'; ') within group (order by T.maildate))end from company r
  join timeline t on (r.company_id = t.adressid)
  join timeline_sent ts on (t.timeline_id = ts.timeline_id)
  where x.kd_nr = t.kd_nr)MAIL_STATUS
from Kunde X
Where x.bestelldatum > ‘20160101’

aber hier bekomme ich den Fehler "Unterabfrage liefert mehr als eine Zeile"....

Hintergrund meiner Abfrage ist folgender. Im Moment werden E-Mails noch von Hand verschickt. Dies soll in Zukunft automatisch passieren. Deshalb benötige ich eine Übersicht der E-Mail Korrespondenzen...

[Wernfried]

Geht es so?

[HIGHLIGHT=sql]
SELECT DISTINCT listagg (t.maildate|| ': ' || r.company_name ||'; ') WITHIN GROUP (ORDER BY T.maildate) OVER (PARTITION BY t.kd_nr) AS MAIL_STATUS, t.kd_nr
[/HIGHLIGHT]

[jum]

Wie bei Deiner letzten Frage stochern wir alle mehr oder weniger im Nebel. Kannst Du uns nicht Beispieldaten und erwartetes Ergebnis zur Verfügung stellen, das spart Zeit und Nerven.

[defo]

Ich habe noch folgende versucht:

..
aber hier bekomme ich den Fehler "Unterabfrage liefert mehr als eine Zeile"....

So, also der Fehler "unterabfrage liefert mehr.." ist systembedingt, wenn für die Case Entscheidung das Datum mit reinkommt (außerhalb des Listagg) und nicht leer und verschieden ist, eben mehr als 1 eindeutiges Datum.

Eine einfach Möglichkeit wäre, das Ganze zu schachteln und so in 2 Stufen durchzulaufen, erst Dein ursprüngliches Statement und dann außen drum die Nullprüfung mit Ersatzausgabe.
Versuch mal:

[highlight=sql]
select kd_nr, name, vorname, nvl(mail_status, 'no E-Mail correspondence')
from (Select x.kd_nr,
x.name,
x.vorname,
(select distinct listagg(t.maildate || ': ' || r.company_name || '; ') within group(order by T.maildate)
from company r
join timeline t
on (r.company_id = t.adressid)
join timeline_sent ts
on (t.timeline_id = ts.timeline_id)
where x.kd_nr = t.kd_nr) MAIL_STATUS
from Kunde X
Where x.bestelldatum > '20160101') y;
[/highlight]

[deyakane]

Deinem CASE-Statement fehlt, meines Achtens, nur die Klausel "END" und 'NO E-MAIL CORRESPONDENCE' statt "NO E-MAIL CORRESPONDENCE" sonst es ist alles korrekt.
In solchen Fällen, verwendet man am besten die Function "DECODE"
....
DECODE(t.mailingdate,
NULL, 'NO E-MAIL CORRESPONDENCE',
t.mailingdate
)