Comment trouver les trous dans une table auto-incrémentée

Version imprimable

17/02/2014, 17h25
ducho

Comment trouver les trous dans une table auto-incrémentée
Bonjour,

Une table contenant 785000 lignes contient des factures (NUMFAC) dont le max est 996791 .
Cette colonne NUMFAC est du type number (6) , le numéro de la facture ne peut donc pas dépasser 1.000.000 .

Nous allons intégrer plus de 4000 factures dans l'application et nous ne pouvons pas toucher au design de l'application , en principe les numéros de facture commencent apres le max précisé ci-dessus .

Je voudrais trouver un trou dans la séquence de numéros de facture de plus de 5000 numéros . (pour forcer le démarrage à un autre numéro sans qu'il puisse y avoir de doublons) .
Il y a environ 28000 "trous" dans la suite de facture .

Comment trouver le trou ou , par exemple , NUMFAC passerait de 20000 à 25000 sans numéro entre les deux, cela doit être simple mais je ne suis pas doué en sql .
C'est l'application qui gére les numéros et non une séquence Oracle ( je sais, c'est nul !!) .
requête pour trouver tous les trous :
Code:

1 2 SELECT (NUMFAC) from tab where (numfac+1) not in (select numfac from tab) order by 1 ;
Cette table a une clé primaire (IDFACTURE) qui malheureusement n'a rien à vor avec ce numfac et qui est alplanumérique (pas de suite logique) .

Evidemment, nous serions bien ennuyés si nous ne trouvions pas de trous et nous demanderons à la société éditrice de corriger cette anomalie .

Cordialement
18/02/2014, 10h05
aieeeuuuuu
Bonjour,

Je mise 1000$ que vous avez effectivement un énorme "trou" : entre -999999 et 0.

faites en bon usage.

Vous pouvez cependant trouver les "trous" dans les nombres positifs grâce à ce type de requête :
Code:

1 2 3 4 5 6 7 8 SELECT * FROM LaTable X WHERE NOT EXISTS( SELECT 1 FROM LaTable Y WHERE Y.NUMFAC BETWEEN X.NUMFAC + 1 AND X.NUMFAC + 5000 )
18/02/2014, 11h06
ducho

Bonjour,

Une facture ne peut pas avoir un numéro négatif.
Votre requête m'a permis de trouver un trou de plus de 40000 dans la liste qui démarre à 151928 .
de même connaissez vous une requête simple pour connaitre le numéro juste aprés ?

en tout cas , merci pour cette requête qui est effectivement toute simple ( elle me retourne aussi le max(numfac) .

Cordialement
18/02/2014, 11h24
aieeeuuuuu

à partir de la requête précédente, vous pouvez faire une auto-jointure sur les factures ayant un numéro plus grand, et prendre le plus petit...

Code:

1 2 3 4 5 6 7 8 9 10 11 12 select min(fe_numfac) from h_factent where fe_numfac > ( SELECT min(fe_numfac) FROM h_factent X WHERE NOT EXISTS ( SELECT 1 FROM h_factent Y WHERE Y.FE_NUMFAC BETWEEN X.FE_NUMFAC + 1 AND X.FE_NUMFAC + 40000 ) )
Cette requête me donne le numéro de facture suivant .

Par curiosié , je vais voir si je peux trouver une requête qui me donne tous les trous avec la valeur des interrvalles .

Merci pour votre aide

Vous pouvez aussi jouer avec les fonctions de fenêtrage :

Code:

1
2
3
4
5
6
7
8
9
10
 
WITH CTE AS (
    SELECT 
        fe_numfac
        ,LEAD(fe_numfac) OVER(ORDER BY fe_numfac) AS Suivant
    FROM h_factent
)
SELECT fe_numfac, Suivant
FROM CTE
WHERE Suivant - fe_numfac > 40000

Citation:

Envoyé par ducho

Par curiosié , je vais voir si je peux trouver une requête qui me donne tous les trous avec la valeur des interrvalles .

Quelque chose comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL>   with gen as (
  2  select level as num from dual connect by level <= 20
  3  ),
  4         data_test as (
  5  select level + 5 as fac from dual connect by level <= 5
  6   union all
  7  select level + 12 as fac from dual connect by level <= 3
  8   union all
  9  select level + 18 as fac from dual connect by level <= 2
 10  ),
 11         grp as (
 12  select row_number() over(order by num) - num as grp, num, fac
 13    from gen g
 14    left join data_test d on d.fac = g.num
 15   where fac is null
 16  )
 17  select grp, min(num), max(num), max(num)-min(num)+1 as longueur
 18    from grp
 19   group by grp;
 
       GRP   MIN(NUM)   MAX(NUM)   LONGUEUR
---------- ---------- ---------- ----------
        -8         16         18          3
         0          1          5          5
        -5         11         12          2

Citation:
Envoyé par aieeeuuuuu
Vous pouvez aussi jouer avec les fonctions de fenêtrage :
Code:

1 2 3 4 5 6 7 8 9 10 WITH CTE AS ( SELECT fe_numfac ,LEAD(fe_numfac) OVER(ORDER BY fe_numfac) AS Suivant FROM h_factent ) SELECT fe_numfac, Suivant FROM CTE WHERE Suivant - fe_numfac > 40000
Cette requête ne renvoie aucune ligne, j'avais bien pensé aux fonctions analytiques , un peu compliqué pouir moi
(il faut que j'appréhende le concept, n'étant pas développeur ...)

18/02/2014, 13h32
aieeeuuuuu

Citation:

Envoyé par ducho

Cette requête ne renvoie aucune ligne,

étrange... et en baissant la limite de "taille de trou" par exemple 5000 au lieu de 40000 ?
18/02/2014, 13h35
ducho

Citation:

Envoyé par aieeeuuuuu

étrange... et en baissant la limite de "taille de trou" par exemple 5000 au lieu de 40000 ?

J'avais essayé , même avec 10 .
aucune ligne renvoyée .

Citation:

Envoyé par skuatamad

Quelque chose comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL>   with gen as (
  2  select level as num from dual connect by level <= 20
  3  ),
  4         data_test as (
  5  select level + 5 as fac from dual connect by level <= 5
  6   union all
  7  select level + 12 as fac from dual connect by level <= 3
  8   union all
  9  select level + 18 as fac from dual connect by level <= 2
 10  ),
 11         grp as (
 12  select row_number() over(order by num) - num as grp, num, fac
 13    from gen g
 14    left join data_test d on d.fac = g.num
 15   where fac is null
 16  )
 17  select grp, min(num), max(num), max(num)-min(num)+1 as longueur
 18    from grp
 19   group by grp;
 
       GRP   MIN(NUM)   MAX(NUM)   LONGUEUR
---------- ---------- ---------- ----------
        -8         16         18          3
         0          1          5          5
        -5         11         12          2

Bonjour,

Merci pour votre requête, malheureusement, je ne la comprends pas .
ma table s'appelle H_FACTENT , ma colonne FE_NUMFAC, je ne sais pas ou mettre ces infos ( vraiment désolé )

Comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
  WITH gen AS (
SELECT level AS num FROM dual connect BY level <= 1e6
),
       grp AS (
SELECT row_number() over(ORDER BY num) - num AS grp, num
  FROM gen g
  LEFT JOIN H_FACTENT d ON d.FE_NUMFAC = g.num
 WHERE FE_NUMFAC IS NULL
)
SELECT grp, min(num), max(num), max(num)-min(num)+1 AS longueur
  FROM grp
 GROUP BY grp;

Citation:

Envoyé par ducho

J'avais essayé , même avec 10 .
aucune ligne renvoyée .

:cfou:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
WITH 
H_FACTENT(FE_NUMFAC) AS(
          SELECT CAST(1 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(2 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(3 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(4 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(6 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(7 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(8 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(15 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(16 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(17 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(100 AS NUMERIC(6)) FROM DUAL
union all   SELECT CAST(101 AS NUMERIC(6)) FROM DUAL
)
,
CTE AS (
    SELECT 
      FE_NUMFAC, 
      LEAD(FE_NUMFAC) OVER(ORDER BY FE_NUMFAC) AS Suivant
 
    FROM H_FACTENT
)
SELECT FE_NUMFAC, Suivant
FROM CTE
WHERE Suivant - FE_NUMFAC > 5;

-->

Code:

1
2
3
4
 
FE_NUMFAC 	SUIVANT
8 		15
17 		100

Citation:

Envoyé par ducho

Cette requête ne renvoie aucune ligne, j'avais bien pensé aux fonctions analytiques , un peu compliqué pouir moi
(il faut que j'appréhende le concept, n'étant pas développeur ...)

méa-culpa , apres beaucoup d'essais et avec toutes vos requêtes , c'est l'éditeur sql que j'utilisais qui ne renvoyait aucune ligne .
la requête :
Code:

1 2 3 4 5 6 7 8 9 10 WITH CTE AS ( SELECT fe_numfac ,LEAD(fe_numfac) OVER(ORDER BY fe_numfac) AS Suivant FROM h_factent ) SELECT fe_numfac, Suivant FROM CTE WHERE Suivant - fe_numfac > 5000
Fonctionne correctement sous TOAD, je n'ai pas encore compris pourquoi .

Un grand merci à vous deux

Citation:
Envoyé par skuatamad
Comme ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 WITH gen AS ( SELECT level AS num FROM dual connect BY level <= 1e6 ), grp AS ( SELECT row_number() over(ORDER BY num) - num AS grp, num FROM gen g LEFT JOIN H_FACTENT d ON d.FE_NUMFAC = g.num WHERE FE_NUMFAC IS NULL ) SELECT grp, min(num), max(num), max(num)-min(num)+1 AS longueur FROM grp GROUP BY grp;
Cette requête fonctionne sous TOAD .
un grand merci à vous deux, les fonctions analytiques me paraissent un petit peu moins nébuleuses .

Dommage que je fasse si rarement du sql .

Cordialement