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 :

Comment remonter le 1er item absent d'une table ?


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 Comment remonter le 1er item absent d'une table ?
    Bonjour,

    La requete suivante remonte les NUCP qui ne sont pas dans la liste fournie en paramétre

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
     
    SELECT NUCP FROM TBCP WHERE NUCP NOT IN ('300030120000020000010 300030120000020000020 300030120000020000030')
    remonte


    300030250000020009991
    300030250000020009992
    300030450000020009900
    300030450000020009901
    300030450000020009990
    ES8601080030200030001000
    ES7101080030200030010000
    ES4401080030200030010001
    300030250000020001511
    300030250000020001495
    BDB01GDB0100020000117
    BIT01GIT0156983652366
    BIT01GIT0100000235687
    300030250010000250001
    BES01GES0100000256003
    300030250015689475396
    BDB01GDB014568569321567
    BIT01GIT015869423512689
    08705260038610405
    08705260037016212
    300030250045869326598
    300030250000023658741
    300030451000020125232
    En fait, je cherche à faire l'inverse :une requete qui me remonte le 1er numéro de la liste qui ne soit pas dans la table.

    Je ne vois pas bien comment m'y prendre en une seule passe. ?

    PS : en terme de perf, vaut-il mieux essayer de faire ceci ou bien un simple select qui vérifie si qqchose remonte est tout aussi performant ? (la liste en paramétre peut avoir une cinquantaine d'elements mais pas bcp plus)

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 728
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 728
    Points : 49 104
    Points
    49 104
    Billets dans le blog
    1
    Par défaut
    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
    WITH 
    T1 AS 
    (SELECT '300030120000020000010' AS N FROM DUAL UNION ALL
     SELECT '300030120000020000020' AS N FROM DUAL UNION ALL
     SELECT '300030120000020000030'),
    T2 AS
    (SElECT N FROM T
     MINUS
     SELECT NUCP FROM TBCP)
    T3 AS
    (SELECT N, ROW_NUMBER() OVER(ORDER BY N) AS RANG
     FROM   T2)
    SELECT N
    FROM   T3
    WHERE  RANG = 1;
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    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,

    Je ne parviens pas à executer cette requete. J'obtiens une erreur

    ORA-00923: mot-clé FROM absent à l'emplacement prévu

    Par ailleurs, si je comprends bien, la partie

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
     
    T1 AS 
    (SELECT '300030120000020000010' AS N FROM DUAL UNION ALL
     SELECT '300030120000020000020' AS N FROM DUAL UNION ALL
     SELECT '300030120000020000030'),
    permet de simuler la table TBCP. Dès lors, il serait plus juste d'indiquer des numéros de cette liste :

    300030250000020009991
    300030250000020009992
    300030450000020009900
    300030450000020009901
    300030450000020009990
    ES8601080030200030001000
    ES7101080030200030010000
    ES4401080030200030010001
    300030250000020001511
    300030250000020001495
    et je ne vois pas bien ensuite à quel endroit dois-je faire figurer lle parametre permettant d'indiquer les numéros dont je cherche à déterminer s'ils sont absent de la table ?

  4. #4
    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,

    Justement non, T1 ne simule pas ta table, mais liste les numéros paramétrés pour la recherche.

    Il manque juste un petit FROM DUAL là-dedans :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    T1 AS 
    (SELECT '300030120000020000010' AS N FROM DUAL UNION ALL
     SELECT '300030120000020000020' AS N FROM DUAL UNION ALL
     SELECT '300030120000020000030' FROM DUAL),
    @SQLPro, tu veux te convertir à Oracle ? Grande nouvelle, et sois le bienvenu !

    (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/

  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
    Maintenant, côté performance, l'important est d'avoir un index sur ta colonne.

    Un petit test :

    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
     
    SQL> create table TBCP as select level NUCP from dual connect by level <= 100000;
     
    Table created.
     
    Elapsed: 00:00:00.29
    SQL> create index tbcpix on tbcp(nucp);
     
    Index created.
     
    SQL> delete from tbcp where nucp = 99999;
     
    1 row deleted.
     
    Elapsed: 00:00:00.04
     
    SQL> delete from tbcp where nucp = 88888;
     
    1 row deleted.
     
    Elapsed: 00:00:00.04
    SQL> commit;
     
    Commit complete.
     
    SQL> set autot on explain
    La requête, avec son plan :

    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
     
    SQL> WITH T1 AS
      2   (SELECT 1 AS N FROM DUAL UNION ALL
      3    SELECT 88888 AS N FROM DUAL UNION ALL
      4    SELECT 99999 FROM DUAL)
      5   SELECT *
      6   FROM T1
      7   WHERE n NOT IN (SELECT nucp
      8              FROM tbcp
      9              WHERE nucp IS NOT NULL)
     10     AND n IS NOT NULL
     11     AND rownum <= 1;
     
             N
    ----------
         88888
     
    Elapsed: 00:00:00.11
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1660097535
     
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |     1 |    17 |     6   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY     |        |       |       |            |          |
    |   2 |   NESTED LOOPS ANTI|        |     2 |    34 |     6   (0)| 00:00:01 |
    |   3 |    VIEW            |        |     2 |     8 |     5  (20)| 00:00:01 |
    |   4 |     UNION-ALL      |        |       |       |            |          |
    |   5 |      FAST DUAL     |        |     1 |       |     2   (0)| 00:00:01 |
    |   6 |      FAST DUAL     |        |     1 |       |     2   (0)| 00:00:01 |
    |   7 |      FAST DUAL     |        |     1 |       |     2   (0)| 00:00:01 |
    |*  8 |    INDEX RANGE SCAN| TBCPIX |     1 |    13 |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter(ROWNUM<=1)
       8 - access("N"="NUCP")
           filter("NUCP" IS NOT NULL)
    Tu noteras que je filtre les NULL, parce que je n'ai pas déclaré la colonne en NOT NULL. Comme les entrées d'index totalement NULL ne sont pas stockées, il n'est pas possible de vérifier la non existance d'une valeur dans cet index si elle est potentiellement nulle...

    (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,

    Comme les paramétres passés à la requete SQL seront envoyé par du code, il ne m'est pas possible de modifier dynamiquement la partie T1 de la requete.

    Auriez-vous une solution qui permette de passer plusieurs paramétres en entrée sans devoir modifier la requete à chaque fois ?

    Sinon, ce n'est pas tres grave : j'ai toujours la possibilité de faire une boucle sur chaque élément de ma liste de paramétre et de boucler jusqu'à ce que la requete ne renvoie rien.


    Cdt

  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
    Salut,

    Sous quel format sont passé les paramètres ?
    Si tu arrives à le passer sous forme de liste, T1 peut donner un truc de ce genre :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    SQL> select column_value n
      2  from table(sys.odcinumberlist(1,88888,99999))
      3  /
     
             N
    ----------
             1
         88888
         99999
    Si c'est sous forme de caractères, il faut "tokenizer" la chose, ce qui se fait par exemple à base de connect by level, instr, et substr.

    (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
    Expert confirmé
    Profil pro
    Inscrit en
    août 2008
    Messages
    2 885
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 2 885
    Points : 5 684
    Points
    5 684
    Par défaut
    Il faut découper la chaine de caractère :
    Lisez Varying in lists pour plus d'information :
    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
    SQL> var txt varchar2(4000);
    SQL> exec :txt := '300030120000020000010,300030120000020000020,300030120000020000030';
     
    PL/SQL procedure successfully completed.
     
    SQL>   with data as (
      2  select level as lvl,
      3         trim( substr (txt,
      4                       instr (txt, ',', 1, level  ) + 1,
      5                       instr (txt, ',', 1, level+1)
      6                       - instr (txt, ',', 1, level) -1 )
      7              ) as token
      8    from (select ','||:txt||',' as txt
      9            from dual)
     10  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
     11  ),
     12         t as (
     13  select lvl, token, row_number() over (order by lvl) as rn
     14    from data
     15   where token not in (select nucp from tbcp)
     16  )
     17  select token
     18    from t
     19   where rn = 1;
     
    TOKEN
    ------------------------------
    300030120000020000010
     
    SQL>

Discussions similaires

  1. Comment rechercher une valeur absente dans une table
    Par Jacno61 dans le forum Requêtes et SQL.
    Réponses: 4
    Dernier message: 24/02/2013, 09h32
  2. comment obtenir la liste des champs d'une table
    Par richard038 dans le forum SQL
    Réponses: 4
    Dernier message: 13/02/2009, 18h28
  3. Réponses: 2
    Dernier message: 29/01/2008, 15h19
  4. Réponses: 2
    Dernier message: 16/05/2006, 14h17
  5. [Débutant]Comment faire des tranches de nombre dans une tabl
    Par Jedecouvreaccess dans le forum Access
    Réponses: 7
    Dernier message: 05/09/2005, 08h46

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