Und gleich nochmal MAX(), diesmal mit MSSQL.

[Ferber]

Guten Abend zusammen !

Es geht schon wieder um Rechnungsnummern. Ja, gesucht hab ich genug! :wall:

Vorab: Was ist die bessere (=schnellere) Formulierung ?

SQL-Code:

Select Top 1 Nummer from Nummern where Datum is null order by Nummer

oder

SQL-Code:

Select MAX(Nummer) from Nummern where Datum is null

Warum warum liefert diese Formulierung einen Syntaxfehler ?

SQL-Code:

SET @Nummer = Select MAX(Nummer) as MaxNr FROM Nummern

Wie krieg ich die grösste Nummer in eine lokale Variable ?

Gibt es im SQL sowas wie einen "Aktuellen Datensatz" ? Bei Triggern gibts ja DELETED und INSERTED.
In einer professionellen Warenwirtschaft hab ich auch herumgestöbert.
Da wird ein Cursor deklariert und mit Fetch werden die Feldwerte in lokale Variablen geholt.
Kurzer Ausschnitt:

SQL-Code:

DECLARE cAdressen CURSOR FOR SELECT Mandant,Adresse,Telefon,Mobilfunk FROM Inserted
OPEN cAdressen
IF @@Cursor_Rows<>0
  BEGIN
    FETCH NEXT FROM cAdressen INTO @Mandant,@Adresse,@Telefon,@Mobilfunk
    WHILE (@@FETCH_STATUS = 0)
    BEGIN 
      ....
    END
  END 
CLOSE cAdressen
DEALLOCATE cAdressen

Geht das nicht einfacher ?

Viele Fragen an die SQL-Gurus. THX im Vorraus ! :lol:

[omata]

Moin,

ich würde diese Formulierung bevorzugen...

SQL-Code:

Select MAX(Nummer) from Nummern where Datum is null

Zitat:

Zitat von Ferber

Warum warum liefert diese Formulierung einen Syntaxfehler?

SQL-Code:

SET @Nummer = Select MAX(Nummer) as MaxNr FROM Nummern

weil da die Klammern fehlen...

SQL-Code:

SET @Nummer = (Select MAX(Nummer) as MaxNr FROM Nummern)

Zitat:

Zitat von Ferber

Wie krieg ich die grösste Nummer in eine lokale Variable ?

Wie jetzt? @Nummer ist eine lokale Variable (also genau wie es oben steht)

Zitat:

Zitat von Ferber

Gibt es im SQL sowas wie einen "Aktuellen Datensatz"?

Das must du genauer spezifizieren.
Meinst du das in einer SQL-Anweisung, Prozedur, Funktion, Trigger,...
Was genau möchtest du machen?

MfG
Thorsten

[marabu]

Auch dir einen guten Abend.

Zitat:

Zitat von Ferber

Vorab: Was ist die bessere (=schnellere) Formulierung ?

SQL-Code:

Select Top 1 Nummer from Nummern where Datum is null order by Nummer

oder

SQL-Code:

Select MAX(Nummer) from Nummern where Datum is null

Bei der ersten fehlt ein abschließendes DESC, sonst sind die beiden Anfragen nicht äquivalent. Ob die eine teurer als die andere ist, das hängt vom Anfrage-Optimierer ab. Ohne einen Blick in den Ausführungsplan traue ich mir da kein Urteil zu.

Zitat:

Zitat von Ferber

Warum warum liefert diese Formulierung einen Syntaxfehler ?

SQL-Code:

SET @Nummer = Select MAX(Nummer) as MaxNr FROM Nummern

Wie krieg ich die grösste Nummer in eine lokale Variable ?

So sollte es funktionieren:

SQL-Code:

declare @Nummer int
set @Nummer = (select max(Nummer) from Nummern)

Zitat:

Zitat von Ferber

Gibt es im SQL sowas wie einen "Aktuellen Datensatz" ?

Intern sicher, nach außen hin ist SQL eine Inkarnation des Relationen-Kalküls und da wird rein mengenorientiert gearbeitet.

Zitat:

Zitat von Ferber

Bei Triggern gibts ja DELETED und INSERTED.

Das sind Pseudo-Tabellen, keine Cursor. Nur bei Cursorn kontrollierst DU die Iteration.

Freundliche Grüße vom marabu

[Ferber]

@Omata, @Marabu: Danke für die schnellen Antworten !
JaJa - die Klammern - bin schon ganz verwirrt ! :wall:
Jetzt läufts ! :cheers:

[alzaimar]

nochwas:

SQL-Code:

select @Nummer = max (Nummer) from Nummern where Datum is null

So würde ich das machen.
Nun zu Deiner Frage:

SQL-Code:

select top 1 Nummer from Nummern where Datum is null order by nummer desc

scheint etwas (aber wirklich nur etwas) besser zu sein, als

SQL-Code:

select max (Nummer) from Nummern where Datum is null

aber nur unter der Voraussetzung, das Nummern und Datum indiziert ist. Ohne das näher zu analysieren würde ich annehmen, das der clustered Index auf dem 'Datum' liegen muss, damit die beiden Versionen identisch sind.

Ansonsten ist Variante 1 (also MAX) eindeutig besser, ohne Index ca. 10x schneller. Mit einfachen Indizes ca 3x. Ich denke, das liegt am Sortieren. Ich hab das rudimentär mit einer 100.000 Tabelle getestet, da tut das Sortieren natürlich weh. ORDER BY wird nur bei clustered indexen ignoriert, denn da liegen die Daten schon richtig sortiert vor.

Unabhängig davon ist es blasphemisch, die nächste Rechnungsnummer so zu erzeugen (finde ich). Denn der Server wird überflüssigerweise mit Arbeit zugeballert (außer, die Indexe stimmen). Stell Dir einfach vor, es wären 100.000.000 Rechnungen (na ja, wer hat die schon, aber egal).

Ich arbeite gerade am gleichen Problem und verwende einen Zähler. Ich habe eine Tabelle mit Rechnungen, die nun fakturiert werden sollen. Ich erzeuge eine temporäre Tabelle über dynamisches SQL, und zwar mit zwei Spalten ('IDRechnung' und 'RechnungsNr'). Die RechnungsNr wird als Identity deklariert, der Seed wird auf die nächste freie Rechnungsnummer gesetzt. Das geht mit plain SQL nicht, also erzeuge ich mir das folgende Statement als String und führe es einfach mit EXEC aus:

SQL-Code:

CREATE TABLE #Foo (
  IDRechnung int, 'ü
  RechnungsNr int identity (12345,1)
 )

Die Tabelle erzeugt also automatisch neue Rechnungsnummern, sobald die Tabelle gefüllt wird, und zwar so:

SQL-Code:

insert into #Foo (IDRechnung) 
  select IDRechnung from Rechnungen where Status='Offen'

Damit habe ich optimal schnell und garantiert fortlaufend alle Rechnungsnummern erzeugt. Danach kopiere ich die Rechungsnummern in die Rechnungstabelle zurück:

SQL-Code:

Update Rechnungen 
   set RechnungsNr = #Foo.RechnungsNr 
  From #Foo
 Where Rechungen.RechungsNr = #Foo.RechungsNr

Zum Schluss nur noch die neue maximal Rechnungsnummer in meine Zählertabelle eintragen und fertig.

SQL-Code:

Update Counters Set Counter = (select max (RechnungsNr)+1 from #Foo) where cntID = 2

[Ferber]

Hi !

Zitat:

Zitat von alzaimar

Unabhängig davon ist es blasphemisch, die nächste Rechnungsnummer so zu erzeugen (finde ich). Denn der Server wird überflüssigerweise mit Arbeit zugeballert ...

Find' ich eigentlich nicht, denn das ist eine zentrale Angelegenheit, die den Client nichts angehen soll,
ausserdem sind's nur ~2000 Nummern pro Jahr.

Zitat:

Zitat von alzaimar

nochwas:

SQL-Code:

select @Nummer = max (Nummer) from Nummern where Datum is null

So würde ich das machen.

Das ist eine klare Formulierung.

Zwischenzeitlich folgendes:

SQL-Code:

Alter Procedure "NewAN" (@NewOnr int Output)
As
  IF EXISTS (Select MAX(Nummer) FROM dbo.AN where Datum is null)
    begin
       DECLARE @Datum DateTime
       select @Datum = cast(GetDate() as int)
       select @NewOnr = max (Nummer) from dbo.AN where Datum is null
       UPDATE dbo.AN SET Datum = @Datum where Nummer = @NewOnr
    end
SELECT * FROM dbo.AN where Nummer = @NewOnr

Fehlermeldung: Prozedur gibt keine Datensätz zurück.
Das Datum wird aber geändert.
Kommentiere ich die Zeile mit dem Update aus, werden Datensätze zurückgegeben.
Dann wird das Datum natürlich nicht geändert, soll aber.
Was mach ich nun schon wieder falsch!? :wall:

Letztstand:

SQL-Code:

Alter Procedure NewAN (@NewOnr int Output)
As
  declare @Datum DateTime
  declare @MaxDt DateTime

  select @Datum = cast(GetDate() as int)  /* nur das Datum extrahieren, Uhrzeit killen */
  select @MaxDt = max(Datum) from dbo.AN  /* grösstes Datum suchen */

  IF Year(@Datum)<>Year(@MaxDt)  /* Jahressprung ? */
     begin
        SET @NewOnr=Year(@Datum)*10000+1    /* Erste Nummer des Jahres generieren, Format yy0001 */
        INSERT dbo.AN ("Nummer", "Datum") VALUES (@NewOnr, @Datum)
     end
  ELSE
    IF EXISTS (Select min(Nummer) FROM dbo.AN where Datum is null) /* Lücke vorhanden ? */
        begin
           select @NewOnr = min (Nummer) from dbo.AN where Datum is null /* Lücke auffüllen */
           UPDATE dbo.AN SET Datum = @Datum where Nummer = @NewOnr
        end
    else
       begin
          select @NewOnr = max (Nummer) from dbo.AN  /* letzte Nummer */
          SET @NewOnr = @NewOnr + 1 /* incrementieren */
          INSERT dbo.AN ("Nummer", "Datum") VALUES (@NewOnr, @Datum)
       end

SELECT * FROM dbo.AN where Nummer = @NewOnr

Das UPDATE funktioniert, das INSERT nicht.
Gibt, nach wie vor, keine Datensätze zurück - is ja auch noch nix verändert worden.

[alzaimar]

Wie rufst Du die Prozedur denn auf? Machst Du das in einer Query? Dann erwartet die natürlich irgend eine Tabelle.
Mach es mit einem TADOCommand oder einer TADOStoredProc und rufe statt Open einfach ExecProc oder Execute (oder wie das heisst) auf.

Das 'blasphemisch' bezieht sich nur auf diese Prämisse:

Zitat:

Zitat von Meine Erfahrung

Gib einem DB-Server keine überflüssigen Suchaufgaben.

die Suche nach der nächsten Re-Nr wäre so eine 'überflüssige Suchaufgabe'. Deshalb überflüssig, weil man es ohne Suchen hinbekommt (nämlich in einer eigenen Tabelle). Aber bei 2000 Nummern pro Jahr gerade noch so verschmerzbar :zwinker:

[Ferber]

@alzaimar: Ich mach das mit TADOStoredProc.ExecProc
Setze ich TADOStoredProc.Active auf true, kommt die Fehlermeldung.

Nochmal: Warum gibt diese StoredProcedure keine Datensätze zurück ?

SQL-Code:

Alter Procedure Test2
As
  update TestTable set Datum=GetDate() where Datum is null
  select * from TestTable

:wall: Ich bräuchte einen Smiley der auf'm Schlauch steht.

[alzaimar]

Zitat:

Zitat von Ferber

@alzaimar: Ich mach das mit TADOStoredProc.ExecProc
Setze ich TADOStoredProc.Active auf true, kommt die Fehlermeldung.

'Active := True' ist das gleiche, wie '.Open'. und das erwartet ein Resultset. Du sollst doch nur 'MyAdoStoredProc.ExecProc' aufrufen...