+ Répondre à la discussion
Affichage des résultats 1 à 8 sur 8
  1. #1
    Candidat au titre de Membre du Club
    Profil pro
    Inscrit en
    mars 2007
    Messages
    49
    Détails du profil
    Informations personnelles :
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : mars 2007
    Messages : 49
    Points : 13
    Points
    13

    Par défaut Requêtes imbriquées et comptage de doublons

    bonjour

    ça fait 1 an que je n'ai plus fait de SQL et ça se sent... j'espère avoir mis le bon titre de post ^^

    ce que je souhaite faire :

    dans une base avec plusieurs shemas,
    pour un schema particulier,
    pour les tables de ce shema contenant une colone particuliere,
    compter le nombre de doublons et afficher la colonne et le resultat du count

    ça paraît super simple à la plupart d'entre vous j'imagine, mais je n'ai jamais fait de requètes imbriquées et ça fait 4h que je galère, donc voici ce que j'ai testé :

    Code :
    select 'owner.table1' AS TABLE_NAME,count(*) FROM owner.table1 WHERE rowid NOT IN (SELECT max(rowid) FROM owner.table1 GROUP BY 'column_key');
    j'obtient bien :

    TABLE_NAME NB
    -------------------- ----------
    owner.table1 26

    et je voudrais faire ça sur toutes les tables du select :

    Code :
    SELECT owner,table_name,column_name FROM ALL_TAB_COLUMNS WHERE owner = 'owner1' and column_name = 'column_key;
    c'est super simple comme concept : j'affiche à priori 1 colonne de résultats et une colonne de calculs sur la 1ère, je pense à un join ou qqch comme ça ?

    j'ai testé ceci mais ça ne marche pas :

    Code :
    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
    DECLARE
    	Cursor listTables IS
    		SELECT owner,table_name 
    		FROM ALL_TAB_COLUMNS 
    		WHERE owner = 'OPS$VTF' and column_name = 'BRCH_CODE';
    	Type t_CurTyp IS Ref Cursor;
    	crsData		t_CurTyp;
    	columnName	varchar(100);
    	lv_string	varchar(100);
    	l_sql		Varchar2(32676);
    BEGIN
    	dbms_output.enable(400000);
    	FOR line IN listTables
    	LOOP
    		lv_string:=('Table ' || line.owner || '.' || line.table_name);
    		dbms_output.put_line(lv_string);
    		l_sql := 'SELECT count(*) FROM ' || line.owner || '.' || line.table_name ||
    		' WHERE rowid NOT IN (SELECT max(rowid) FROM ' || line.owner || '.' || line.table_name ||
    		' GROUP BY "BRCH_CODE")';
    		Open crsData FOR l_sql USING line.table_name;
    		LOOP
    			FETCH crsData INTO columnName;
    			EXIT WHEN crsData%NOTFOUND;
    			lv_string:=('Table ' || line.owner || '.' || line.table_name || ' : ' || columnName || ' lignes');
    			dbms_output.put_line(lv_string);
    		END LOOP;
    		Close crsData;
    	END LOOP;
    END;
    /
    enfin si ça marche une fois, ça plante au bout de 15 lignes puis ça refuse de refonctionner, j'ai peur de casser qqch si j'insiste :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Table OPS$VTF.BDT_C_DUE_DATE_ADV
    Table OPS$VTF.BDT_C_DUE_DATE_ADV : 0 lignes
    Table OPS$VTF.BDT_C_NOT_ADV
    Table OPS$VTF.BDT_C_NOT_ADV : 0 lignes
    Table OPS$VTF.BDT_P_ECR_PC
    Table OPS$VTF.BDT_P_ECR_PC : 26 lignes
    DECLARE
    *
    ERROR at line 1:
    ORA-01006: bind variable does not exist
    ORA-06512: at line 20
    avec pour ligne 20:
    20 Open crsData FOR l_sql USING line.table_name;


    je ne sais plus quoi tester... please helppp

  2. #2
    McM
    McM est déconnecté
    Expert Confirmé Sénior
    Inscrit en
    juillet 2003
    Messages
    3 652
    Détails du profil
    Informations forums :
    Inscription : juillet 2003
    Messages : 3 652
    Points : 5 046
    Points
    5 046

    Par défaut

    A quoi sert le USING ?

    Vu que tu construit ta requête complètement sans binder, tu ne devrais faire qu'un simple
    More Code : More Bugs. Less Code : Less Bugs

  3. #3
    McM
    McM est déconnecté
    Expert Confirmé Sénior
    Inscrit en
    juillet 2003
    Messages
    3 652
    Détails du profil
    Informations forums :
    Inscription : juillet 2003
    Messages : 3 652
    Points : 5 046
    Points
    5 046

    Par défaut

    PS : Pour les doublons, il est plus simple de faire comme suit
    Code :
    SELECT COUNT(*) FROM (SELECT 1 FROM table_name GROUP BY BRCH_CODE HAVING COUNT(*) > 1)
    Ta requête de doublons compte visiblement le nombre de lignes en doublons (et pas le nombre de brch_code différent ayant des doublons). Ce serait plutôt
    Code :
     SELECT SUM(nb) FROM (SELECT COUNT(*) - 1 nb FROM table_name GROUP BY BRCH_CODE HAVING COUNT(*) > 1)
    More Code : More Bugs. Less Code : Less Bugs

  4. #4
    Expert Confirmé Sénior
    Inscrit en
    août 2008
    Messages
    2 189
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 189
    Points : 4 029
    Points
    4 029

    Par défaut

    En associant la nouvelle "requête à la mode" et la remarque de MCM on peut tout faire en SQL :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
      with t as (
    SELECT owner,table_name, column_name 
      FROM ALL_TAB_COLUMNS 
     WHERE owner       = 'OPS$VTF' 
       AND column_name = 'BRCH_CODE'
    )
    SELECT table_name,
           nvl(to_number(extractvalue(
                       dbms_xmlgen.getXMLtype ('SELECT SUM(nb) as cnt 
                                                  FROM (SELECT COUNT(*) - 1 nb 
                                                          FROM '|| owner ||'.'|| table_name ||
                                                       ' GROUP BY '||column_name||' HAVING COUNT(*) > 1)'),
                                               '/ROWSET/ROW/CNT'))
               ,0) as doublons_par_table
      FROM T
    Mais ça n'est probablement pas très performant.
    Regarde les commentaires autour de cette approche sur le blog de Laurent Schneider notamment pour les limitations, comme les tables IOT.

  5. #5
    Candidat au titre de Membre du Club
    Profil pro
    Inscrit en
    mars 2007
    Messages
    49
    Détails du profil
    Informations personnelles :
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : mars 2007
    Messages : 49
    Points : 13
    Points
    13

    Par défaut

    Citation Envoyé par skuatamad Voir le message
    En associant la nouvelle "requête à la mode" et la remarque de MCM on peut tout faire en SQL :

    Mais ça n'est probablement pas très performant.
    Regarde les commentaires autour de cette approche sur le blog de Laurent Schneider notamment pour les limitations, comme les tables IOT.
    heuuuu merci je vais lire ça cet après midi !!
    en tous cas cette fois ça fonctionne, ça me donne un beau tableau avec les noms des tables et le "nombre" de doublons.

    Enfin doublons, j'en sais trop rien. Ma requète initiale venait d'un autre forum qui expliquait que le champ rowid était fait pour cela.

    avec vos exemples de requètes, j'obtient des résultats forts différents:
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    select 'OPS$VTF.DBT_C_BANK' AS TABLE_NAME,count(*) AS NB FROM OPS$VTF.DBT_C_BANK WHERE rowid NOT IN (SELECT max(rowid) FROM OPS$VTF.DBT_C_BANK GROUP BY 'BRCH_CODE');
     
    TABLE_NAME                 NB
    ------------------ ----------
    OPS$VTF.DBT_C_BANK     153040
     
    SELECT 'OPS$VTF.DBT_C_BANK' AS TABLE_NAME,COUNT(*) FROM (SELECT 1 FROM OPS$VTF.DBT_C_BANK GROUP BY BRCH_CODE HAVING COUNT(*) > 1);
     
    TABLE_NAME           COUNT(*)
    ------------------ ----------
    OPS$VTF.DBT_C_BANK          5
     
    SELECT 'OPS$VTF.DBT_C_BANK' AS TABLE_NAME,SUM(nb) FROM (SELECT COUNT(*) - 1 nb FROM DBT_C_BANK GROUP BY BRCH_CODE HAVING COUNT(*) > 1);
    TABLE_NAME            SUM(NB)
    ------------------ ----------
    OPS$VTF.DBT_C_BANK     153036
    moi mon critère c'est le champ BRCH_CODE, là dessus je cherche les lignes en doublons pour un même BRCH_CODE.
    Pourquoi j'ai 3 résultats différents ???
    je vais devoir analyser le code de ces 3 requètes sur une table qui contient peu d'enregistrements pour comprendre...

  6. #6
    Candidat au titre de Membre du Club
    Profil pro
    Inscrit en
    mars 2007
    Messages
    49
    Détails du profil
    Informations personnelles :
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : mars 2007
    Messages : 49
    Points : 13
    Points
    13

    Par défaut

    bon alors en fait...
    j'ai refait ces tests sur une table avec seulement 5 enregistrements, et il m'apparaît que........ mon select et le second select de McM donnent le résultat attendu, merci McM et merci skuatamad pour votre expertise et votre temps !!

    Le tableau de skuatamad est très joli merci encore ! Même si je vais avoir du mal à comprendre le code...

    maintenant j'aimerai trouver de vraies lignes en doublon dans leur totalité, pas seulement sur un champ.
    j'imagine que le code va être plus gros et plus ardu, ce sera un autre sujet.

    résolu bravo pour votre réactivité et la qualité des réponses !!!

  7. #7
    Expert Confirmé Sénior
    Inscrit en
    août 2008
    Messages
    2 189
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 189
    Points : 4 029
    Points
    4 029

    Par défaut

    maintenant j'aimerai trouver de vraies lignes en doublon dans leur totalité, pas seulement sur un champ.
    Pour ça il suffit de concatener les colonnes avec la virgule en séparateurs.

    Si tu es en 11GR2 il y a listagg :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
      WITH t AS (
    SELECT owner,table_name, listagg(column_name,',') WITHIN GROUP (order by column_id) as liste_colonne
      FROM ALL_TAB_COLUMNS 
     WHERE owner = 'OPS$VTF' 
     group by owner,table_name
    )
    SELECT table_name,
           nvl(to_number(extractvalue(
                       dbms_xmlgen.getXMLtype ('SELECT SUM(nb) as cnt 
                                                  FROM (SELECT COUNT(*) - 1 nb 
                                                          FROM '|| owner ||'.'|| table_name ||
                                                       ' GROUP BY '||liste_colonne||' HAVING COUNT(*) > 1)'),
                                               '/ROWSET/ROW/CNT'))
               ,0) AS doublons_par_table
      FROM T
    Mais je doute que tu ais cette chance donc il faut regarder d'autres méthodes, comme celle du XML (je réécris que le WITH):
    Code :
    1
    2
    3
    4
    5
    6
    7
      WITH t AS (
    SELECT owner,table_name, 
           rtrim(REPLACE(REPLACE(XMLAGG(XMLElement("x", column_name) ORDER BY column_id ASC),'<x>', ''),'</x>', ','),',') as liste_colonne
      FROM ALL_TAB_COLUMNS 
     WHERE owner = 'OPS$VTF' 
     group by owner,table_name
    )
    Sinon pour toujours plus de méthode d'aggrégation des colonnes regarde :
    String Aggregation Techniques
    Et choisis celle que tu comprends le mieux et pour laquelle tu as les droits de création/exécution

    PS : il faudra sûrement filtrer la requête dans le WITH pour exclure les PK voir les UK
    Regarde pour ça ALL_CONSTRAINTS

  8. #8
    Membre Expert
    Avatar de islamov2000
    Homme Profil pro islamov islamov
    Ingénieur d'études & developpement en informatique
    Inscrit en
    septembre 2007
    Messages
    640
    Détails du profil
    Informations personnelles :
    Nom : Homme islamov islamov
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2007
    Messages : 640
    Points : 1 231
    Points
    1 231

    Par défaut dbms_xmlgen

    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

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

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •