IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Sélectionner les N premiers numéros libres ?


Sujet :

SQL Oracle

  1. #1
    Membre à l'essai
    Profil pro
    Inscrit en
    juin 2007
    Messages
    36
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2007
    Messages : 36
    Points : 24
    Points
    24
    Par défaut Sélectionner les N premiers numéros libres ?
    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.

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Ingénieur d'études décisionnel
    Inscrit en
    mai 2002
    Messages
    8 730
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 8 730
    Points : 29 105
    Points
    29 105
    Par défaut
    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é.
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Membre émérite Avatar de pacmann
    Homme Profil pro
    Consulté Oracle
    Inscrit en
    juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : juin 2004
    Messages : 1 626
    Points : 2 845
    Points
    2 845
    Par défaut
    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

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  4. #4
    Membre à l'essai
    Profil pro
    Inscrit en
    juin 2007
    Messages
    36
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2007
    Messages : 36
    Points : 24
    Points
    24
    Par défaut
    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)."

  5. #5
    Membre émérite Avatar de pacmann
    Homme Profil pro
    Consulté Oracle
    Inscrit en
    juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : juin 2004
    Messages : 1 626
    Points : 2 845
    Points
    2 845
    Par défaut
    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

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  6. #6
    Membre à l'essai
    Profil pro
    Inscrit en
    juin 2007
    Messages
    36
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2007
    Messages : 36
    Points : 24
    Points
    24
    Par défaut
    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 !

  7. #7
    Membre émérite Avatar de pacmann
    Homme Profil pro
    Consulté Oracle
    Inscrit en
    juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : juin 2004
    Messages : 1 626
    Points : 2 845
    Points
    2 845
    Par défaut
    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

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  8. #8
    Modérateur

    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    8 121
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata

    Informations forums :
    Inscription : septembre 2008
    Messages : 8 121
    Points : 16 351
    Points
    16 351
    Par défaut
    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

  9. #9
    Membre à l'essai
    Profil pro
    Inscrit en
    juin 2007
    Messages
    36
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2007
    Messages : 36
    Points : 24
    Points
    24
    Par défaut
    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 !

  10. #10
    Membre à l'essai
    Profil pro
    Inscrit en
    juin 2007
    Messages
    36
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2007
    Messages : 36
    Points : 24
    Points
    24
    Par défaut
    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 ?

  11. #11
    Membre émérite Avatar de pacmann
    Homme Profil pro
    Consulté Oracle
    Inscrit en
    juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : juin 2004
    Messages : 1 626
    Points : 2 845
    Points
    2 845
    Par défaut
    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 ?)

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  12. #12
    Membre à l'essai
    Profil pro
    Inscrit en
    juin 2007
    Messages
    36
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2007
    Messages : 36
    Points : 24
    Points
    24
    Par défaut
    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.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [AC-2013] Sélectionner les x premiers enregistrements avec un paramètre
    Par Barzy dans le forum Requêtes et SQL.
    Réponses: 4
    Dernier message: 14/07/2015, 22h13
  2. Sélectionner les 10 premiers enregistrements
    Par Yanmeunier dans le forum Requêtes et SQL.
    Réponses: 1
    Dernier message: 16/04/2008, 10h23
  3. Réponses: 2
    Dernier message: 05/04/2007, 14h19
  4. Sélectionner les N premiers mots d'un champ texte
    Par keskispas dans le forum SQL
    Réponses: 19
    Dernier message: 22/01/2007, 14h22
  5. Premier numéro libre pour champ numéroAuto
    Par yancimer dans le forum Access
    Réponses: 8
    Dernier message: 11/09/2006, 11h43

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo