PL SQL: Aggregatsfunktionen

[46AND2]

Hallo

Ich habe eine Tabelle namens TITLEQUOTES. Sie ist mit Börsenschlusskursen gefüllt und hat als Felder eine ID (für das Wertpapier), Q_DATE (für das Datum) und QUOTE (für den Kurs).
Die Börsen sind in der Regel nur Montags bis Freitags offen, je nach Feiertag und je nach Land kann eine oder mehrere Börse auch unter der Woche geschlossen sein.

Mit

SELECT COUNT(*) FROM TITLEQUOTES WHERE Q_DATE = MAX(Q_DATE)

kann ich die Anzahl Kurse des aktuellsten Datums aus TITLEQUOTES herauslesen.

Nun kann es sein, dass am aktuellsten Datum nur 1 Kurs eingelesen ist. In diesem Fall möchte ich, dass es mir den gleichen SELECT auf das zweitaktuellste Datum macht.

Ich habe in PL SQL folgendes gedacht (ist nicht der ganze Code hier):

...
-- zaehler und anzahlKurse wurden oben als INT deklariert
zaehler := 0;
anzahlKurse := 0;
while anzahlKurse <= 1 loop
-- Die Variable anzahlKurse wird dann später im Skript ausgewertet.
SELECT COUNT(*) INTO anzahlKurse FROM TITLEQUOTES WHERE Q_DATE = MAX(Q_DATE-zaehler);
zaehler := (zaehler + 1);
END LOOP;
....

Nur funktioniert das leider nicht. Sagen wir, MAX(Q_DATE) sei ein Montag, mit nur 1 Kurs. Der Sonntag wird bestimmt keinen Kurs haben. So kriegt er irgendwie Probleme, weil das Datum des Sonntags nicht in der Tabelle vorkommt...

Ich habe schon vieles ausprobiert mit NULL-Funktionen, CASE etc... Ich blick nicht mehr durch. Kann mir jemand bitte helfen?

Vielen Dank!
Gruss
Marco

[Noemi]

Hallo,

ich habe nur wenig Zeit um Dir zu antworten, aber probiere es mal mit:

if( TRUNC('25-JUL-95','DAY') ='MONDAY') dann machst du einen 2. variable, q_date2, und gibst den wert d – 2 -> dann hast du mal friday. und
dann mach ein if, und wenn dann dein anzahlkurse zu klein ist, gehst du auf else.

Noémi

-----------
Entschuldigung für mein Deutsch, aber die ist ja nicht meine Muttersprache....

[46AND2]

Ciao Noemi

Danke vielmals für Deine Antwort.

Wie ich in meinem Post oben erwähnt habe, gibt es auch Feiertage unter der Woche. Je nach Feiertag hat es dann Kurse oder nicht.

Deshalb funktioniert die "Logik" (Montag bis Freitag okay, Samstag und Sonntag nicht) nur beschränkt.

Gibt es keinen anderen Weg?

Dankeschön und Gruss
Marco

[SiegfriedEhlert]

Angaben präzisieren

Hallo 46AND2.

Wenn Du eine sinnvolle Antwort haben möchtest, solltest Du Dein Problem möglichst präzise beschreiben. Mit dem von Dir formulierten Statement
SELECT COUNT(*) FROM TITLEQUOTES WHERE Q_DATE = MAX(Q_DATE)
kannst Du nichts aus der Tabelle herauslesen, außer der Fehlermeldung "ORA-00934: Gruppenfunktion ist hier nicht zulässig". Selbst mit einem syntaktisch korrekten Statement
SELECT count(*)
from TITLEQUOTES q
where Q_DATE =(
SELECT max(Q_DATE)
from TITLEQUOTES t
where t.ID = q.ID
)
würdest Du immer 1 als Ergebnis erhalten, wenn sich der Primary Key z. b. aus ID und Q_DATE zusammensetzt.

Also, das "Create Table"-Skript und ein paar Testdatensätze veröffentlichen, dann steigt Deine Chance auf eine sinnvolle Antwort.

Gruß, Siegfried

[46AND2]

Hallo Siegfried

Danke für Deine Antwort!

Sorry, mein Fehler. Ich bin manchmal ein bisschen voreilig und vergiss wichtige Angaben, SORRY!

Ich habe folgende Tabelle (inkl. ein paar Inserts):

CREATE TABLE AAA_TITLEQUOTES
(
  ID        NUMBER(9)   NOT NULL,
  BANK		NUMBER(9)	NOT NULL,
  Q_DATE    DATE        NOT NULL,
  QUOTE     FLOAT(126)
);
-- inserts für BANK = 200
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(10, 200, '10.07.2007', 50);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(11, 200, '10.07.2007', 60);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(10, 200, '11.07.2007', 51);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(11, 200, '11.07.2007', 61);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(10, 200, '13.07.2007', 52);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(11, 200, '13.07.2007', 62);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(10, 200, '15.07.2007', 53);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(11, 200, '15.07.2007', 63);
-- inserts für BANK = 300
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(10, 300, '10.07.2007', 50);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(11, 300, '10.07.2007', 60);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(10, 300, '11.07.2007', 51);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(11, 300, '11.07.2007', 61);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(10, 300, '13.07.2007', 52);
INSERT INTO AAA_TITLEQUOTES(ID, BANK, Q_DATE, QUOTE)
VALUES(11, 300, '15.07.2007', 63);
-- commit
COMMIT;

Diese Tabelle beinhaltet folgende Felder:

ID ist die Wertschriftenpapiernummer
BANK ist die Kursquelle
Q_DATE ist das Kursdatum
QUOTE ist der Kurs des Wertschriftenpapiers.

Wenn man das obere Skript ausgeführt hat, sieht man, dass für die BANK = 300 am 13. und 15. Juli jeweils nur 1 Kurs in der Tabelle steht.

Nun möchte ich mit folgendem Aufruf

CALL COPY_TITLEQUOTES(200,300)

(Prozedur weiter unten) folgendes erreichen:

1. Suche das aktuellste Datum der Kursquelle 200, das mindestens einen Kurs enthält.
2. Suche das aktuellste Datum der Kursquelle 300, welches mehr als einen Kurs enthält.
3. Lösche alle Daten der Kursquelle 300 vom Datum aus 2. bis und mit Datum aus 1.
4. Kopiere alle Daten der Kursquelle 200 in die 300 vom Datum aus 2. bis und mit Datum aus 1.

Nun habe ich dazu folgende Prozedur geschrieben, die leider nicht funktioniert, weil sobald das Datum des 12.07.2007 abgefragt wird, das COUNT(*) keinen Wert zurückliefert.

CREATE OR REPLACE PROCEDURE copy_titlequotes (quelle INT, dest INT)
IS
   anzahlkurse     INT;
   zaehler         INT;
   maxdatequelle   DATE;
   datum           DATE;
BEGIN
   -- 1. Max(q_date) in quelle
   SELECT MAX (q_date)
     INTO maxdatequelle
     FROM aaa_titlequotes
    WHERE bank = quelle;

   -- 2. Find max(q_date) with more than 1 quotes in DESTINATION
   anzahlkurse := 0;
   zaehler := 0;

   WHILE anzahlkurse <= 1
   LOOP
      SELECT   COUNT (quote)
          INTO anzahlkurse
          FROM aaa_titlequotes
         WHERE bank = dest AND q_date = (SELECT (MAX (q_date) - zaehler)
                                           FROM aaa_titlequotes
                                          WHERE bank = dest)
      GROUP BY bank;

      zaehler := (zaehler + 1);
   END LOOP;

   SELECT (MAX (q_date) - zaehler + 1)
     INTO datum
     FROM aaa_titlequotes
    WHERE bank = dest;

   -- 3 Copy quotes
   -- 3.1 Deleting the quotes first
   DELETE FROM aaa_titlequotes
         WHERE bank = dest AND q_date > datum AND q_date <= maxdatequelle;

   COMMIT;

   -- 3.2 Inserting the new ones
   INSERT INTO aaa_titlequotes
               (ID, bank, q_date, quote)
      SELECT ID, dest, q_date, quote
        FROM aaa_titlequotes
       WHERE q_date > datum AND q_date <= maxdatequelle AND bank = quelle;

   COMMIT;
END copy_titlequotes;
/

Das ganze sollte natürlich nicht nur mit der Einschränkung "mehr als 1 Kurs", sondern mit beliebig vielen funktionieren.

Ich wäre sehr, sehr dankbar, wenn mir jemand helfen kann.

Besten Dank!
Gruss
Marco

[SiegfriedEhlert]

1. Versuch einer sinnvollen Antwort

Hallo Marco,

die Problembeschreibung ist schon wesentlich genauer. Ich versuche mal zu rekapitulieren, wie ich Dich verstanden habe.

1. Suche das aktuellste Datum der Quelle, mit mindestens einem Kurs
2. Suche das aktuellste Datum des Ziels, mit mehr als einem Kurs
3. Lösche alle Daten des Ziels mit Q_DATE zwischen (2) und (1)
4. Kopiere alle Daten der Quelle mit Q_DATE zwischen (2) und (1) ins Ziel

Meine Lösung sieht dann so aus:

CREATE or replace Procedure COPY_TITLEQUOTES(
		from_Bank	AAA_TITLEQUOTES.BANK%type	-- 200
	,	to_Bank		AAA_TITLEQUOTES.BANK%type	-- 300
)
is
	d_from	date;
	d_to	date;
BEGIN
--	(1) Aktuellste Datum der Quelle, mit mindestens einem Kurs 
	SELECT	max(Q_DATE)
	into	d_from
	from	AAA_TITLEQUOTES
	where	BANK = from_Bank	-- 200
	and	Q_DATE is not NULL
	;	-- 15.07.2007

--	(2) Aktuellste Datum des Ziels, mit mehr als einem Kurs
	SELECT	max(d)
	into	d_to
	from(	SELECT	Q_DATE	d
		,	count(*)
		from	AAA_TITLEQUOTES
		where	BANK = to_Bank	-- 300
		and	QUOTE is not NULL
		group by Q_DATE
		having count(*) > 1
	) 
	;	-- 11.07.2007

--	Lösche alle Daten des Ziels mit Q_DATE zwischen (2) und (1)
	DELETE	AAA_TITLEQUOTES
	where	BANK = to_Bank
	and	Q_DATE between d_to and d_from
	;
	--	ID | Datum
	--	---|-----------
	--	10 | 11.07.2007
	--	10 | 13.07.2007
	--	11 | 11.07.2007
	--	11 | 15.07.2007

--	Kopiere alle Daten der Quelle mit Q_DATE zwischen (2) und (1) ins Ziel
	INSERT into AAA_TITLEQUOTES
		SELECT	ID
		,	to_Bank
		,	Q_DATE
		,	QUOTE
		from	AAA_TITLEQUOTES
		where	BANK = from_Bank
		and		Q_DATE between d_to and d_from
	;
	--	ID | Datum
	--	---|-----------
	--	10 | 11.07.2007
	--	10 | 13.07.2007
	--	10 | 15.07.2007
	--	11 | 11.07.2007
	--	11 | 13.07.2007
	--	11 | 15.07.2007
	COMMIT;
END;
/

Und jetzt die Probe auf's Exemple
column ID format 99
column Bank format 9999
column quote format 99999
select * from AAA_TITLEQUOTES order by 1,2,3

ID Bank Q_DATE quote
--- ----- -------- ------
10 200 10.07.07 50
10 200 11.07.07 51
10 200 13.07.07 52
10 200 15.07.07 53
10 300 10.07.07 50
10 300 11.07.07 51
10 300 13.07.07 52
11 200 10.07.07 60
11 200 11.07.07 61
11 200 13.07.07 62
11 200 15.07.07 63
11 300 10.07.07 60
11 300 11.07.07 61
11 300 15.07.07 63

14 rows selected.

exec COPY_TITLEQUOTES(200, 300 )

PL/SQL procedure successfully completed.

select * from AAA_TITLEQUOTES order by 1,2,3

ID Bank Q_DATE quote
--- ----- -------- ------
10 200 10.07.07 50
10 200 11.07.07 51
10 200 13.07.07 52
10 200 15.07.07 53
10 300 10.07.07 50
10 300 11.07.07 51
10 300 13.07.07 52
10 300 15.07.07 53
11 200 10.07.07 60
11 200 11.07.07 61
11 200 13.07.07 62
11 200 15.07.07 63
11 300 10.07.07 60
11 300 11.07.07 61
11 300 13.07.07 62
11 300 15.07.07 63

16 rows selected.

Ich hoffe, das Ergebnis stimmt mit Deinen Erwartungen überein

Siegfried

[46AND2]

Hi Siegfried

Was soll ich sagen? GENIAL!!! Genau das habe ich gesucht!!!

Du kannst Dir nicht vorstellen, wie dankbar ich Dir bin. Heute gehe ich in die Ferien und ich musste unbedingt eine Lösung dazu haben! Erreicht!

Danke vielmals, dass Du Dir die Zeit und Mühe genommen hast!

Machs gut
Marco