Discussion :

[david2109]

Bonjour,

J'ai une table ORACLE dans laquelle par suite d'ajouts et suppressions, il existe des numéros de séquence disponibles.

Par exemple : TABLE TBPRNI

IDPRNI;LILPRNI
3532;BANCS PD DE
3533;BANCS PD CH
3534;BANCS PD BE
3537;PRNIV INT ES
3539;PRNIV INT ES
3540;PRNIV DOM ES
3541;PRNIV DOM IT
3542;PRNIV PD ES
3543;PRNIV INT GB

Les numéros 3535 3536 3538 3544 et au-delà sont donc disponibles

Je sais récupérer le 1er numéro disponible de ma table.

SELECT MIN(A.IDPRNI) + 1
FROM TBPRNI A WHERE A.IDPRNI + 1
NOT IN (SELECT B.IDPRNI FROM TBPRNI B)

renvoie 3535 (correct)


Mais j'ai besoin de récupérer les N premiers numéros disponibles ? N pouvant varier selon le contexte.

Si N=3, la requete SQL doit retourner 3535 3536 et 3538
Si N=5, la requete SQL doit retourner 3535 3536 3538 3544 et 3545

Je ne parviens pas à construire cette requete.
Une idée ?

PS : J'ai besoin que les 3 numéros soient remontées d'un seul coup. Impossible d'aller chercher le 1er numéro disponible, de commiter, de chercher le 2eme 1er numero dispo, de commiter, etc.

[al1_24]

J'ai la flemme de rechercher comment générer une liste d'entiers (la première sous-requête), mais le principe est là :

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
WITH    nbr
    AS  (   SELECT  1   num FROM DUAL
        UNION   SELECT  2   FROM DUAL
        ...
        UNION   SELECT  :n  FROM DUAL
        )
    ,   lst
    AS  (   SELECT  src.idprni + nbr.num    dispo
                ,   ROW_NUMBER() OVER (ORDER BY src.idprni + nbr.num)   ord
            FROM    tbprni  src
                CROSS JOIN
                    nbr
            WHERE   NOT EXISTS
                    (   SELECT  NULL
                        FROM    tbprni  xst
                        WHERE   xst.idprni = src.idprni + nbr.num
                    )
        )
SELECT  lst.dispo
FROM    lst
WHERE   lst.ord IN  
        (   SELECT  num
            FROM    nbr   
        )
;

Comme cela, tu récupères le plus d'identifiants possibles dans chaque série de "trous", dans la limite du nombre que tu as précisé.

[pacmann]

Salut,

En faisant la différence avec le suivant, que tu obtiens avec la fonction analytique lead, tu sais pour chaque ligne quels sont les trous qui la succède.

L'astuce CONNECT BY permet de répliquer les lignes autant de fois qu'il n'y a de trous (Merci Waldar et Skuat, c'est un truc que j'ai appris récemment ici )

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
WITH TBPRNI AS (
SELECT column_value idprni
FROM table(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)
, u as (select idprni, lead(idprni, 1) over(order by idprni) - idprni  n
        from tbprni)
select idprni + level
from u
where n > 1
connect by level < n
and prior idprni = idprni
and PRIOR sys_guid() IS NOT NULL

[david2109]

Bonjour,

Merci pour vos réponses.

Concernant la première possibilité, j'ai oublié de préciser que la requete SQL sera appelée par du code et qu'il sera donc difficile de la modifier dynamiquement.
La partie

UNION SELECT 2 FROM DUAL
...
UNION SELECT :n FROM DUAL

pose donc problème.
En outre, cette requête renvoie toujours 3535 ?!


Concernant la seconde possibilité, pas simple effectivement mais cela fonctionne mieux

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
WITH TBPRNI AS (
SELECT column_value idprni
FROM TABLE(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)
, u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM tbprni)
SELECT idprni + level
FROM u
WHERE n > 1
connect BY level < n
AND prior idprni = idprni
AND PRIOR sys_guid() IS NOT NULL

j'obtiens la liste de tous les trous et après, je peux me débrouiller.

Par contre, dans la requete, les numéros idprni 3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543 sont en dur.
L'exemple que j'ai fourni n'est qu'un extrait. En pratique la table TBPRNI est trop importante pour mettre les numéros en dur.

Est-il possible de faire en sorte que la requete récupère la liste des numéros utilisés ? J'ai essayé - sait-on jamais ? - un "select distinct from tbprni mais ca ne marche pas (ca serait trop simple)."

[pacmann]

La partie

Code :

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

1
2
3
4
WITH TBPRNI AS (
SELECT column_value idprni
FROM TABLE(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)

... ne servait qu'à émuler la création de la table TBPRNI de mon côté, et tester la solution. De ton côté, tu peux omettre cette partie et te contenter de la suite de la requête.

Code :

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

1
2
3
4
5
6
7
8
9
 
WITH u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM tbprni)
SELECT idprni + level
FROM u
WHERE n > 1
connect BY level < n
AND prior idprni = idprni
AND PRIOR sys_guid() IS NOT NULL

[david2109]

Bonjour,

Merci cela fonctionne ! J'obtiens la liste de tous les identifiants disponibles dans la table TBPRNI

Une dernière question : comment et où amender la requete pour qu'elle me renvoie la liste des 5 premiers identifiants disponibles ? (le chiffre pouvant être un paramètre)

Je ne veux pas abuser mais il y a trop de notions que je dois d'abord approfondir dans cette requete !

[pacmann]

Pour récupérer les N premiers, tu peux les numéroter avec une fonction analytique, puis filtrer sur ce rang :

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
 
WITH TBPRNI AS (
SELECT column_value idprni
FROM TABLE(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)
, u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM tbprni)
SELECT *
FROM (
    SELECT idprni + level, row_number() over (order by idprni + level) rk
    FROM u
    WHERE n > 1
    connect BY level < n
    AND prior idprni = idprni
    AND PRIOR sys_guid() IS NOT NULL
    )
WHERE rk <= 2

[Waldar]

Une autre solution, un peu plus verbeuse que celle de pacmann :

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
WITH TBPRNI AS -- Vos données
(
SELECT column_value as idprni
  FROM TABLE(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)
  ,  PARAM as -- Votre nombre de lignes à récupérer
(
select 7 as num from dual
)
  ,  SR as -- Calcul de la plage d'id utilisés
(
select min(idprni) as idprni_min, max(idprni) as idprni_max
  from TBPRNI
)
  ,  TBPRNI_FULL AS -- Génération de la liste complète des id
(
    select idprni_min + level - 1 as idprni
      from SR
connect by level <= idprni_max - idprni_min + 1 + (select num from param)
)
  ,  DELTA as -- Soustraction de la liste complète avec la liste actuelle
(
select idprni
  from TBPRNI_FULL 
 minus
select idprni
  from TBPRNI
)
  ,  SR2 as -- Numérotation ordonnée des id disponibles
(
select idprni
     , row_number() over(order by idprni asc) as rn
  from delta
)
-- Sélection finale
select idprni
  from SR2
 where rn <= (select num from param);
 
IDPRNI
------
  3535
  3536
  3538
  3544
  3545
  3546
  3547

[david2109]

Bonjour,

Merci bcp ! Honnetement, j'en aurais été incapable aussi vite!

Apres retrait de la partie émulation, cela donne ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
WITH u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM tbprni)
SELECT IDPRNI_DISPO
FROM (
    SELECT idprni + level as IDPRNI_DISPO, row_number() over (ORDER BY idprni + level) rk
    FROM u
    WHERE n > 1
    connect BY level < n
    AND prior idprni = idprni
    AND PRIOR sys_guid() IS NOT NULL
    )
WHERE rk <= 4

Le chiffre 4 pouvant facilement être donné en paramétre.

Encore merci !

[david2109]

Bonjour,

Une question subsidaire :

Que faut-il modifier à la requete pour qu'elle remonte le 1er identifiant (IDPRNI 1) s'il est dispo ?

Dans l'exemple fourni, le 1er identifiant utilisé est le 3532

Potentiellement, j'ai donc tous les identifiants de 1 à 3531 également disponibles.

Existe-il une solution pour les remonter également dans la requete ?

[pacmann]

Le plus simple, c'est de faire comme si 0 était dans ta table

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
 
WITH u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM (select idprni 
                 from  tbprni
                 union 
                 select 0
                 from dual)
SELECT IDPRNI_DISPO
FROM (
    SELECT idprni + level AS IDPRNI_DISPO, row_number() over (ORDER BY idprni + level) rk
    FROM u
    WHERE n > 1
    connect BY level < n
    AND prior idprni = idprni
    AND PRIOR sys_guid() IS NOT NULL
    )
WHERE rk <= 4

(On doit aussi pouvoir se torturer pour changer la requête, mais est-ce bien utile ?)

[david2109]

Parfait, exactement ce dont j'ai besoin.

Il y a une petite coquille (parenthese manquante)
Voici la requete corrigé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
 
 
WITH u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM (SELECT idprni 
                 FROM  tbprni
                 union 
                 SELECT 0
                 FROM dual))
SELECT IDPRNI_DISPO
FROM (
    SELECT idprni + level AS IDPRNI_DISPO, row_number() over (ORDER BY idprni + level) rk
    FROM u
    WHERE n > 1
    connect BY level < n
    AND prior idprni = idprni
    AND PRIOR sys_guid() IS NOT NULL
    )
WHERE rk <= 4

Evidemment non, inutile d'aller plus loin. Parfois le mieux est l'ennemi du bien.