StoredProcedure Problem

[DirkG]

Hallo Leute!
Wieder einmal ein Problem zwischen meinen Ohren...

Also folgendes würde ich gerne erreichen. Um ein Rechnungsnummer zu erzeugen, wurde bisher auf der Client-Seite die Nummer zusammengestellt und geprüft, ob diese noch frei ist. Die Nummer basiert auf einem Schlüssel, der wie folgt aus sieht:

Code:

yyyy mm xx
---- -- --
 |    |  |.. Laufende Nummer 01-99
 |    |..... Nummer des aktuellen Monat
 |.......... Nummer des aktuellen Jahr

Nun war meine Überlegung, das ganze durch den Server mit einer StoredProcedure machen zu lassen.
Die einzelnen Funktionen, um an die Werte zu kommen habe schon:

SQL-Code:

SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE;
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE;
SELECT COUNT(RNUMBER) FROM Rechnungen WHERE (RNUMBER LIKE ('yyyymm%'));

Nun das Problem. Wie bokomme ich die einzelnen Werte in den Rückgabewert der Procedure und wie stelle ich den String-Ausdruck für die letzte Abfrage zusammen? Ich hatte gedacht, es so machen zu können, aber IB hat da was dagegen:

SQL-Code:

SET TERM ^ ;

CREATE PROCEDURE MglGetNUMBER RETURNS (NEWNUMBER VARCHAR(20)) AS
BEGIN 
  lyear VARCHAR(4);
  lmonth VARCHAR(2);
  lid VARCHAR(2);

  SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP) INTO :lyear FROM RDB$DATABASE;
  SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) into :lmonth FROM RDB$DATABASE;
  SELECT COUNT(RNUMBER) FROM Rechnungen WHERE (RNUMBER LIKE (lyear+lmonth+'%')) INTO :lid;

  SELECT lyear+lmonth+lid INTO :NEWNUMBER;

  SUSPEND;
END
^

COMMIT WORK ^
SET TERM ;^

[Mr_G]

Such mal nach RETRUN und CONCAT... ich denke das müsste helfen.

[mkinzler]

SQL-Code:

SELECT lyear || lmonth || lid INTO :NEWNUMBER;

[DirkG]

:gruebel: RETRUN und CONACT
bist du da sicher in der Onlinehilfe zu sql ist kein Eintrag mit CONACT

[Mr_G]

Oje... das tut mir leid... ist eine MySQL-Funktion. :oops:
Sorry!

[mkinzler]

Zitat:

Zitat von DirkG

:gruebel: RETRUN und CONACT
bist du da sicher in der Onlinehilfe zu sql ist kein Eintrag mit CONACT

RETURN ist bei IB/FB SUSPEND und CONCAT mit Hilfe des SQL-Konkatenators ||

[DirkG]

@Mr_G
Is OK. Hatte ich schon mir gedacht.

@mkinzler

Zitat:

SELECT lyear || lmonth || lid INTO :NEWNUMBER;

Das scheint mir richtig zu sein. Die IB-Console meckert noch in der Zeile 3
TOKEN UNKNOW VARCHAR
Keine Ahnung warum. Sollte doch so aussehen?

[Wunni]

eventuell so?

SQL-Code:

CREATE PROCEDURE MGLGETNUMBER
RETURNS (
    NEWNUMBER VARCHAR(20))
AS
declare variable y char(4);
declare variable m char(2);
declare variable lfdnr integer;
begin
  y = extract(year from current_timestamp);
  m = extract(month from current_timestamp);

  select count(rnumber)+1 from Rechnungen
  where rnumber starting with :y||:m
  into :lfdnr;
  /* mit count ist das allerdings nicht sehr elegant,
  da die Nummern ja nicht fortlaufend existieren werden */

  newnumber = :y||:m||:lfdnr;

  suspend;
end

[mkinzler]

Oder ganz ohne lokale Variablen

[DirkG]

@mkinzler

Zitat:

Oder ganz ohne lokale Variablen

Wäre schön gewesen doch bekomme ich das so nicht hin, da ja die Suchanfrage für die bisher vergebenen Nummern mit den Werten von Monat und Jahr gefüllt werden muss. Ich versuche mal folgendes.

SQL-Code:

SET TERM ^ ;

CREATE PROCEDURE GetNUMBER RETURNS (NEWNUMBER VARCHAR(10)) AS
DECLARE VARIABLE lyear VARCHAR(4);
DECLARE VARIABLE lmonth VARCHAR(2);
DECLARE VARIABLE lid VARCHAR(2);
BEGIN
  /* Jahr und Monat für die Suche ermitteln */
  SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP) INTO :lyear FROM RDB$DATABASE;
  SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) INTO :lmonth FROM RDB$DATABASE;
  /* Suche Anzahl der bisher in Jahr+Monat erstellten Rechnungen */
  SELECT COUNT(RNUMBER) FROM Rechnungen WHERE (RNUMBER LIKE (lyear||lmonth||'%')) INTO :lid;
  /* Setze neue Rechnungsnummer zusammen */
  SELECT lyear||lmonth||lid INTO :NEWNUMBER;
  SUSPEND;
END
^

COMMIT WORK ^
SET TERM ;^

[DirkG]

Panik :wall:
Jetzt meckert IB-Console an INTO rum.
Solangsam habe ich keinen Plan mehr..

[mkinzler]

SQL-Code:

NEWNUMBER = lyear || lmonth|| lid;

[DirkG]

Is richtig, aber problem bleibt immer noch...

SQL-Code:

SET TERM ^ ;

CREATE PROCEDURE GetNUMBER RETURNS (NEWNUMBER VARCHAR(10)) AS
 DECLARE VARIABLE lyear VARCHAR(4);
 DECLARE VARIABLE lmonth VARCHAR(2);
 DECLARE VARIABLE lid VARCHAR(2);
BEGIN
 /* Jahr und Monat für die Suche ermitteln */
 SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP) [color=#ff0000]INTO[/color] :lyear FROM RDB$DATABASE; /* Hier gehts nicht weiter */
 SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) INTO :lmonth FROM RDB$DATABASE;
 /* Suche Anzahl der bisher in Jahr+Monat erstellten Rechnungen */
 SELECT COUNT(RNUMBER) FROM Rechnungen WHERE (RNUMBER LIKE (lyear||lmonth||'%')) INTO :lid;
 /* Setze neue Rechnungsnummer zusammen */
 NEWNUMBER = lyear||lmonth||lid;
 SUSPEND;
END
^

COMMIT WORK ^
SET TERM ;^

[mkinzler]

Es fehlt das For

SQL-Code:

for select ... INTO ...

[Wunni]

das into :variable muss immer am Ende stehen!!!

Beispiel:

SQL-Code:

CREATE PROCEDURE MGLGETNUMBER 
RETURNS ( 
    NEWNUMBER VARCHAR(20)) 
AS 
declare variable y char(4); 
declare variable m char(2); 
declare variable lfdnr integer; 
begin 
  y = extract(year from current_timestamp); 
  m = extract(month from current_timestamp); 

  select count(rnumber)+1 from Rechnungen 
  where rnumber starting with :y||:m 
  into :lfdnr; 
  /* mit count ist das allerdings nicht sehr elegant, 
  da die Nummern ja nicht fortlaufend existieren werden */ 

  newnumber = :y||:m||:lfdnr; 

  suspend; 
end

[Wunni]

Zitat:

Es fehlt das For

bei count() kommt immer nur ein Datensatz zurück, daher benötigt man kein for select. Es reicht ein einfacher select.

[omata]

SQL-Code:

CREATE PROCEDURE MGLGETNUMBER
RETURNS (NEWNUMBER VARCHAR(10))
AS
DECLARE VARIABLE y CHAR(4);
DECLARE VARIABLE m CHAR(2);
DECLARE VARIABLE lfdnr INT;
BEGIN
  y = EXTRACT(YEAR FROM current_timestamp);
  m = EXTRACT(MONTH FROM current_timestamp);

  SELECT COUNT(*)+1 
  FROM rechnungen
  WHERE rnumber STARTING WITH :y || :m
  INTO :lfdnr;

  IF (:lfdnr < 10) THEN
    newnumber = :y || :m || '0' || :lfdnr;
  ELSE
    newnumber = :y || :m || :lfdnr;

  SUSPEND;
END

[DirkG]

@omata
Vielen Dank für deine Erweiterung.

@Alle
Ich habe seit gestern ein wenig probiert. Es scheint das es nun zufriedenstellend funktioniert. Also Danke für die schnelle Hilfe und für alle hier noch einmal die komplette Umsetzung.

Die Anwendung besitzt nun keine Kontrolle mehr über die Rechnungsnummer. Die komplette Logik für die Vergabe liegt nun ausschliesslich beim Server. Der Ablauf ist der folgende. Die Stored procedure "regetnumber" liefert die neue Nummer, diese wird mit dem Trigger "re_add" in das entsprechende Feld im Datensatz eingetragen, wenn der Datensatz erzeugt wird. Damit diese nicht mehr geändert werden kann, setzt der zweite Trigger "re_set" die alte Nummer immer wieder ein, auch wenn sie geändert wurde. Zum Schluss noch zwei Trigger "re_evmsg_add" und "re_evmsg_set", die die Anwendung über die Änderungen informieren.

!!!Achtung!!!
Achtet darauf, das dieses System der Nummernerstellung nicht mehr konform mit den Anforderungen des Finanzamtes für eine lückenlose Nummerierung von Rechnungen ist. (Vielen Dank an WoGe für diesen Hinweis).

SQL-Code:

SET TERM ^ ;

/* Stored procedures */
CREATE PROCEDURE regetnumber RETURNS(newnumber VARCHAR(10))AS
DECLARE VARIABLE y CHAR(4);
DECLARE VARIABLE m CHAR(2);
DECLARE VARIABLE lfdnr INT;
BEGIN
  y = EXTRACT(YEAR FROM current_timestamp);
  m = EXTRACT(MONTH FROM current_timestamp);

  SELECT COUNT(rnumber)+1 FROM rechnungen 
    WHERE rnumber STARTING WITH :y || :m INTO :lfdnr;

  IF (:lfdnr < 10) THEN newnumber = :y || :m || '0' || :lfdnr;
                   ELSE newnumber = :y || :m || :lfdnr;

  SUSPEND;
END
 ^

/* Triggers only will work for SQL triggers */
CREATE TRIGGER re_add FOR rechnungen ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
  :
  :
  SELECT * FROM regetnumber INTO NEW.ReNumber;
  :
  :
END
 ^

CREATE TRIGGER re_set FOR rechnungen ACTIVE BEFORE UPDATE POSITION 0 AS
BEGIN
  NEW.ReNumber = OLD.ReNumber;
  :
  :
END
 ^

CREATE TRIGGER re_evmsg_add FOR rechnungen ACTIVE AFTER INSERT POSITION 0 AS
BEGIN
  POST_EVENT 'TabChange_Rechnungen';
END
 ^

CREATE TRIGGER re_evmsg_set FOR rechnungen ACTIVE AFTER UPDATE POSITION 0 AS
BEGIN
  POST_EVENT 'TabChange_Rechnungen';
END
 ^

SET TERM ; ^
COMMIT WORK;