SQL-Abfrage: Lücke finden

[Allrounder]

Das Problem ist folgendes:

In einer Tabelle gibt es unter anderem die Spalte LaufendeNummer.
Sie kann die Werte 1-999 enthalten. Jede Nummer kann nur einmal vorkommen. Nicht alle Werte sind vorhanden.

Jetzt brauche ich als Rückgabewert eines SQL-Statements die erste (kleinste) nicht verwendete LaufendeNummer.

Beispiel:

Inhalt der Spalte:
001
002
003
009
010
012
013
etc.

Die SQL-Abfrage soll mir die 4 liefern.

Jemand eine Idee?

[Fuerchau]

Für solche Aktionen habe ich mir eine kleine Hilfstabelle mit allen Zahlen von 1 bis gewünschtem Maximum erstellt (in diesem Fal von 1 - 999.999).

Per

Select MyNum from MyHelp H
where not exists (select * from MyTable T where H.MyNum = T.NumField)
fetch first row only

Das Risiko der Doppelbelegung bei Parallelausführung habe ich über Sperren verhindert.

[Allrounder]

Danke so etwas Ähnliches hatte ich mir auch überlegt.

Habe gerade noch eine andere Lösung:

select min(LaufendeNummer) +1 from tabelle a where not exeists ( select * from tabelle b where .... and a.LaufendeNummer +1 = b.LaufendeNummer)

Scheint zu funktionieren.

[B.Hauser]

Wenn Du auf Release V5R4 oder höher bist, kannst Du auch wie folgt vorgehen:

With Counter (Nbr) as (   Select 1 from SysIbm/SysDummy1
                       Union All
                          Select Nbr + 1 from Counter
                             Where Nbr < 1000)
Select Min(Nbr) 
   from Counter exception join MyTable on Nbr = LfdNbr;;

Ab 6.1 kannst du anstatt SysIbm/SysDummy1 auch Values verwenden:

With Counter (Nbr) as (Values(1)
                       Union All
                          Select Nbr + 1 from Counter
                             Where Nbr < 1000)
Select Min(Nbr) 
   from Counter exception join MyTable where Nbr = LfdNbr;;

Birgitta

[Allrounder]

Hallo Birgitta,

Deine Lösung für 6.1 gefällt mir und funktioniert auch sehr gut ... vorausgesetzt man ersetzt das "where" durch "on" ;-).

Die Lösung ist sicher auch performanter.

Vielen Dank dafür.
LG Allrounder

[Fuerchau]

Performant ist relativ.
Für 100 Sätze über Value mag das wohlstimmen, für 1.000.000 Sätze eher unwahrscheinlich.
Zu bedenken ist, dass nämlich die rekursive CTE erst komplett aufgebaut werden muss und ggf. ein temporärer Index erstellt wird (soweit das bei internen *QUERY-Objekten überhaupt passiert).
Hat man diese aber als Ergebnis bereits verfügbar, dürfte das mit Tabelle schneller gehen.

[B.Hauser]

Hallo Birgitta,

Deine Lösung für 6.1 gefällt mir und funktioniert auch sehr gut ... vorausgesetzt man ersetzt das "where" durch "on" ;-).

Die Lösung ist sicher auch performanter.

Vielen Dank dafür.
LG Allrounder

Sorry, kommt davon, wenn man ein SQL statement gerade mal schnell runterklimpert ohne auszuprobieren

Birgitta

[Allrounder]

Performant ist relativ.
Für 100 Sätze über Value mag das wohlstimmen, für 1.000.000 Sätze eher unwahrscheinlich.
Zu bedenken ist, dass nämlich die rekursive CTE erst komplett aufgebaut werden muss und ggf. ein temporärer Index erstellt wird (soweit das bei internen *QUERY-Objekten überhaupt passiert).
Hat man diese aber als Ergebnis bereits verfügbar, dürfte das mit Tabelle schneller gehen.

Ich meinte auch performanter als meine Lösung mit der "mit sich selbst gejointen" Tabelle. Eine Tabelle mit den Werten 1 - 999 permanent anzulegen, könnte wirklich schneller sein, mit Birgittas Lösung spare ich ein Objekt.
Die Performance werde ich im Auge behalten.

Nochmals vielen Dank euch beiden.