[Erledigt] Seltsamer Fehler bei einem Oracle-SQL

[Donald]

Hallo,

ich habe einen Select, der unter Oracle 10g immer den Fehler:

ORA-00904: "MON": ungültiger Bezeichner

bringt:

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS MON
FROM tblarchive 
WHERE SUBPROVIDERID=1 
GROUP BY MON

Kann mir jemand helfen, was daran denn falsch ist? Ich kann MON in irgendwas anderes umbenennen, es ist immer der selbe Fehler

Donald

[wolf29]

Hm, hast du Mon auch in Anführungszeichen (1Keydata - Home of Free Online Tutorials, oder wie genau sieht dein Code aus dafür?

Wolf29

[Donald]

Hallo Wolf29,

Ich kann den Query direkt im Oracle XE Webclient austesten und dort kommt schon der Fehler. Es ist völlig unabhängig von meinem PHP Code.

Ich habe auch schon die Anführungszeichen versucht, aber es klappt nix:

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS "MON"
FROM tblarchive 
WHERE SUBPROVIDERID=1 
GROUP BY "MON"

Genau das selbe...

Es scheint fast, als könnte Oracle nicht nach einem Alias-Namen gruppieren. Kann das sein? Wie sonst soll ich denn nach einer Datumsformatierung gruppieren??? Distinct fällt leider aus, da ich dort keine Anzahl der gruppierten Datensätze mit COUNT(*) erhalten kann.

Donald

[wolf29]

Eigentlich schon (siehe hier: https://www.courses.psu.edu/ist/ist2...tm#columnalias). Hm..vielleicht ist das Problem, dass Du auf MON keinen Zugriff hast, weil erst der FROM Teil ausgeführt/ausgewertet wird( schau mal hier die Diskussion, etwas tiefer: [Frage] Was bedeutet "missing expression"? [Archive] - Informatik-Forum.at)

wolf29

[Donald]

Hallo,

Die Links bringen mich leider nicht weiter. Mein Query ist ja extrem simpel. Unter MySQL kann ich das Problemlos nachbilden und ausführen:

SELECT DATE_FORMAT(STARTDATE, "%m/%y") AS MON
FROM tblarchive
WHERE SUBPROVIDERID=1
GROUP BY MON

Geht einwandfrei. Wie bekomme ich das nach Oracle?

Donald

[thomas_w]

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS MON
FROM tblarchive 
WHERE SUBPROVIDERID=1 
GROUP BY MON

Wie bekomme ich das nach Oracle?

Der GROUP BY nach dem Alias geht nicht.

Lösung:

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS MON
FROM tblarchive 
WHERE SUBPROVIDERID=1 
GROUP BY 1;

oder

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS MON
FROM tblarchive 
WHERE SUBPROVIDERID=1 
GROUP BY TO_CHAR(STARTDATE, 'MM/YY');

Grüße
Thomas

[Donald]

Hallo Thomas,

danke, der Tipp mit dem sortieren über den Ausdruck hat geholfen. So hab ich es dann hinbekommen. Leider wird eine ORDER BY Klausel auf eine nicht gruppierte Spalte dann nicht akzeptiert, aber es geht auch so.

Wow, dass Oracle sowas nicht kann hätte ich nicht erwartet

Donald

[thomas_w]

Leider wird eine ORDER BY Klausel auf eine nicht gruppierte Spalte dann nicht akzeptiert, aber es geht auch so.

Habe ich nicht verstanden. Beispiel?

Grüße
Thomas

[Donald]

Hi Thomas,

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS MONTH, 
       TO_CHAR(STARTDATE, 'MM/YY') AS LABEL, 
       COUNT(*) AS ANZ
FROM tblarchive 
WHERE SUBPROVIDERID=1
GROUP BY TO_CHAR(STARTDATE, 'MM/YY')
ORDER BY STARTDATE

Gibt einen Fehler ORA-00979: Kein GROUP BY-Ausdruck. Wenn ich die OrderBy Klausel durch

ORDER BY TO_CHAR(STARTDATE, 'MM/YY')

ersetze, dann geht es. Ich bin mir nur nicht sicher, ob es das gleiche Ergebnis ist, weil ein ORDERBY STARTDATE sicher nach chronologischem Ablauf sortieren würde. Die Sortierung nach TO_CHAR() wird vermutlich alphabetisch sortieren, was nicht das gleiche sein muss.

Oracle kann bei GROUP BY aber nur nach einem Gruppierungsausdruck sortieren. Den habe ich aber nicht

Donald

[thomas_w]

Stimmt, die Sortierung ist nur korrekt, wenn das komplette Startdate verwendet wird.

Wie wäre es mit..

CREATE TABLE test_daten (
 startdate DATE NOT NULL
);

INSERT INTO test_daten VALUES
( TO_DATE('2010-04-01','YYYY-MM-DD') );

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS MONTH, 
       TO_CHAR(STARTDATE, 'MM/YY') AS LABEL, 
       COUNT(*) AS ANZ
FROM test_daten
GROUP BY STARTDATE
ORDER BY STARTDATE;

MONTH	LABEL	ANZ
-----------------------------------
04/10	04/10	1

Grüße
Thomas

[Donald]

Hallo Thomas,

das geht leider nicht. Wenn ich in deinem Beispiel noch mehr Daten einfüge kannst Du sehen warum:

INSERT INTO test_daten VALUES
( TO_DATE('2010-04-03','YYYY-MM-DD') );

INSERT INTO test_daten VALUES
( TO_DATE('2010-04-08','YYYY-MM-DD') );

INSERT INTO test_daten VALUES
( TO_DATE('2010-05-01','YYYY-MM-DD') );

INSERT INTO test_daten VALUES
( TO_DATE('2010-02-01','YYYY-MM-DD') );

MONTH LABEL ANZ
---------------------
02/10 02/10 1
04/10 04/10 1
04/10 04/10 1
04/10 04/10 1
05/10 05/10 1


Gesucht wird aber (Wunschergebnis):
MONTH LABEL ANZ
--------------------
02/10 02/10 1
04/10 04/10 3
05/10 05/10 1

Es muss also nach Monat sortiert und gruppiert werden. Und das bekomme ich im Moment nicht hin

Donald

[Donald]

Ich glaube ich habs:

SELECT z1.* FROM (

SELECT TO_CHAR(STARTDATE, 'MM/YY') AS MONTH, 
       TO_CHAR(STARTDATE, 'MM/YY') AS LABEL
FROM test_daten
ORDER BY STARTDATE ASC

) z1
GROUP BY MONTH

Ist das so korrekt sortiert? Irgendwie ist es komisch, dass ein Ändern von
ORDER BY STARTDATE ASC auf ORDER BY STARTDATE DESC die Reihenfolge nicht umkehrt. Sortiert GROUP BY nochmal nach?

Donald

[thomas_w]

Den SQL muss ich mir noch in Ruhe überlegen (wird heute später).

Der ORDER BY im SUB-Select bringt vermutlich nichts. Per SQL-Standard sortiert der GROUP BY die Ausgabe nicht. Zum Sortieren muss explizit ein ORDER BY geschrieben werden.

MySQL macht automatisch zu einem GROUP BY ein passendes ORDER BY.

Grüße
Thomas

[thomas_w]

Mögliche Lösung

SELECT TO_CHAR(STARTDATE, 'YYYY/MM') AS SORT,
       MAX(TO_CHAR(STARTDATE, 'MM/YY')) AS MONTH,
       MAX(TO_CHAR(STARTDATE, 'MM/YY')) AS LABEL,
       COUNT(*) AS ANZ
  FROM test_daten
 GROUP BY TO_CHAR(STARTDATE, 'YYYY/MM')
 ORDER BY TO_CHAR(STARTDATE, 'YYYY/MM');

SORT	MONTH	LABEL	ANZ
---------------------------------------------
2010/02	02/10	02/10	1
2010/04	04/10	04/10	3
2010/05	05/10	05/10	1

Grüße
Thomas

[Donald]

Hallo Thomas,

Jaaa, das geht! Clever, eine extra Spalte nur für die Gruppierung anzulegen die dann eine andere Sortierung hat (Jahr und dann Monat). Super Idee!

Danke!

Donald