Discussion :

[ducho]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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
)

[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

[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...

[ducho]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[aieeeuuuuu]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[skuatamad]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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