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

[ducho]

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

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

[aieeeuuuuu]

Cette requête ne renvoie aucune ligne,

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

[ducho]

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

[ducho]

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

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é )

[skuatamad]

Comme ça :

Code :

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

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;

[aieeeuuuuu]

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

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

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

1
2
3
4
 
FE_NUMFAC 	SUIVANT
8 		15
17 		100

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

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 > 5000

Fonctionne correctement sous TOAD, je n'ai pas encore compris pourquoi .

Un grand merci à vous deux

[ducho]

Comme ça :

Code :

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

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