Ajouter des «lignes manquantes» dans un SELECT

Version imprimable

Ajouter des «*lignes manquantes*» dans un SELECT

Bonjour,

Je regrette ma question, qui doit trouver sa réponse sur le forum, mais j’ignore même comment faire ma recherche.

Voilà, après une requête de sélection (dans une table de log), je me retrouve avec la table suivante :

Nº de semaine ---- changements
32 ---- 2
35 ---- 3
36 ---- 8
38 ---- 3

et il me faudrait modifier la requête pour qu’elle me renvoie :

Nº de semaine ---- changements
32 ---- 2
33 ---- 0
34 ---- 0
35 ---- 3
36 ---- 8
37 ---- 0
38 ---- 3

C’est à dire, à l’intérieur de la plage de dates passées en paramètre, compléter les Nº de semaines —*obtenus avec datepart ('ww',…)*— manquants (là où il n’y a pas eu de changements).

Je travail sur Oracle.

Pour info :

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
select
tabA.sem semaine
, decode (tabA.NBRE_MODIFS, null,0,tabA.NBRE_MODIFS) NBRE_MODIFS
, decode (tabB.NBRE_MODIFS_SIEGE ,null,0,tabB.NBRE_MODIFS_SIEGE) NBRE_MODIFS_SIEGE
from
(
SELECT tab1.sem sem, COUNT(C_BPARTNER_ID) NBRE_MODIFS
FROM
(
        SELECT DISTINCT
        DATEPART('ww',l.created) as sem,
        M.C_BPARTNER_ID c_bpartner_id,
        COUNT(L.AD_CHANGELOG_ID) NBRE_MODIFS
        FROM AD_CHANGELOG L
          INNER JOIN AD_TABLE T ON (L.AD_TABLE_ID = T.AD_TABLE_ID)
          INNER JOIN AD_COLUMN C ON (L.AD_COLUMN_ID = C.AD_COLUMN_ID)
          -- Auteur de la modification
          INNER JOIN AD_USER U ON L.CREATEDBY = U.AD_USER_ID
            INNER JOIN AD_ORG UUSER ON U.AD_ORG_ID = UUSER.AD_ORG_ID
          -- Membre modifié
          LEFT JOIN C_BPARTNER M ON L.RECORD_ID = M.C_BPARTNER_ID
            LEFT JOIN AD_ORG UT ON M.AD_ORG_ID = UT.AD_ORG_ID
        WHERE
        ut.ad_org_id = '1000878' and /**/
        T.TABLENAME = 'C_BPartner'                              -- On ne prend que les changements à la fiche Tiers
        AND UUSER.VALUE <> '000'                                -- On ne compte pas les modifs du siège
        AND C.COLUMNNAME IN ('IsActive','Sexe','Date_Naiss','Z_Fonction_ID','Email_tiers','Name','Assurance','M_Warehouse_ID','Tel_tiers','CurrentMembre','Handicap','Name2','Membre','Mode_envoi','GSM_tiers','Publimailing')
        AND NOT (L.OLDVALUE = 'NULL' AND L.NEWVALUE IS NULL)    -- On ignore les erreurs dimport
	AND L.CREATED >= to_date('01/09/2010','DD/MM/YYYY')  /**/
	AND L.CREATED <= to_date('01/10/2010','DD/MM/YYYY')  /**/
       group by DATEPART('ww',l.created), M.C_BPARTNER_ID
    )tab1
group by tab1.sem
    )tabA
    left join
    (SELECT tab2.sem sem, COUNT(C_BPARTNER_ID) NBRE_MODIFS_SIEGE from(
        SELECT DISTINCT
        DATEPART('ww',l.created) as sem,
        M.C_BPARTNER_ID c_bpartner_id,
        COUNT(L.AD_CHANGELOG_ID) NBRE_MODIFS
        FROM AD_CHANGELOG L
          INNER JOIN AD_TABLE T ON (L.AD_TABLE_ID = T.AD_TABLE_ID)
          INNER JOIN AD_COLUMN C ON (L.AD_COLUMN_ID = C.AD_COLUMN_ID)
          -- Auteur de la modification
          INNER JOIN AD_USER U ON L.CREATEDBY = U.AD_USER_ID
            INNER JOIN AD_ORG UUSER ON U.AD_ORG_ID = UUSER.AD_ORG_ID
          -- Membre modifié
          LEFT JOIN C_BPARTNER M ON L.RECORD_ID = M.C_BPARTNER_ID
            LEFT JOIN AD_ORG UT ON M.AD_ORG_ID = UT.AD_ORG_ID
        WHERE
        ut.ad_org_id = '1000878' and      /**/
        T.TABLENAME = 'C_BPartner'                              -- On ne prend que les changements à la fiche Tiers
        AND UUSER.VALUE = '000'                                -- On ne compte pas les modifs du siège
        AND C.COLUMNNAME IN ('IsActive','Sexe','Date_Naiss','Z_Fonction_ID','Email_tiers','Name','Assurance','M_Warehouse_ID','Tel_tiers','CurrentMembre','Handicap','Name2','Membre','Mode_envoi','GSM_tiers','Publimailing')
        AND NOT (L.OLDVALUE = 'NULL' AND L.NEWVALUE IS NULL)    -- On ignore les erreurs dimport
	AND L.CREATED >= to_date('01/09/2010','DD/MM/YYYY')      /**/
	AND L.CREATED <= to_date('01/10/2010','DD/MM/YYYY')      /**/
       group by DATEPART('ww',l.created), M.C_BPARTNER_ID
    )tab2
group by tab2.sem
    )tabB on tabB.sem = tabA.sem
order by tabA.sem

09/11/2010, 10h03
Waldar

Quelle version d'Oracle ?

Fonction datepart ? Quel intérêt ?
09/11/2010, 10h56
Zenji

Là n’est pas mon problème, et j’ai envie de dire que la version d’Oracle n’intervient pas vraiment non plus puisque ma question concerne plus le language SQL dans sa standardisation que Oracle lui-même.

Mais pour répondre à ta question, DATEPART parce-que c’est une fonction que je maîtrise… et qui fonctionne partout où j’ai besoin de faire des GROUP BY avec des transformations de date, je ne vais donc pas perdre du temps (et celui de mon employeur) à en utiliser une « autre ». Je ne l’indique dans la question que pour informer que le Nº de semaine n’est pas une donnée contenue en «*dur » dans la DB.

Et je suis donc en 10.2.0.4.0

Pourquoi je vous demande votre version d'Oracle ?
Car les syntaxes évoluent de version en version, on peut faire des choses en 10g qu'on ne pouvait pas faire en 8i, et ça va influencer sur la réponse.

Pourquoi je vous demande l'intérêt de datepart ?
Vous avez parfaitement le droit, mais coder une fonction qui nécessite une installation alors qu'elle existe en natif sous couvert de gagner du temps, je trouve que votre argument fallacieux.

Revenons à votre problème.
Ce qu'il vous faut c'est - vous l'avez identifié - une liste des semaines qui vous permettra de construire une jointure externe.

L'idéal serait de posséder une table calendrier dans votre modèle.
Si vous n'en n'avez pas, vous pouvez toujours en simuler une à partir de votre plage de date.

Enfin, vous faites deux fois la même requête pour traiter les utilisateurs siege et non siege. Vous pouvez avec un simple case ne faire ce travail qu'une fois.

Essayez ceci :

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
31
32
33
34
35
36
37
38
39
40
41
42
43
With Req as
(
  SELECT DATEPART('ww',l.created) AS sem,
         case UUSER.VALUE when '000' then 1 else 0 end as user_siege, 
         count(distinct M.C_BPARTNER_ID) as NBRE_MODIFS
    FROM AD_CHANGELOG L
         INNER JOIN AD_TABLE T
           ON T.AD_TABLE_ID = L.AD_TABLE_ID
         INNER JOIN AD_COLUMN C
           ON C.AD_COLUMN_ID = L.AD_COLUMN_ID
          -- Auteur de la modification
         INNER JOIN AD_USER U
           ON U.AD_USER_ID = L.CREATEDBY
         INNER JOIN AD_ORG UUSER
           ON UUSER.AD_ORG_ID = U.AD_ORG_ID
          -- Membre modifié
         LEFT OUTER JOIN C_BPARTNER M
           ON M.C_BPARTNER_ID = L.RECORD_ID
         LEFT OUTER JOIN AD_ORG UT
           ON UT.AD_ORG_ID = M.AD_ORG_ID
   WHERE ut.ad_org_id = '1000878'
     AND T.TABLENAME = 'C_BPartner'                        -- On ne prend que les changements à la fiche Tiers
     AND C.COLUMNNAME IN ('IsActive','Sexe','Date_Naiss','Z_Fonction_ID','Email_tiers','Name','Assurance','M_Warehouse_ID','Tel_tiers','CurrentMembre','Handicap','Name2','Membre','Mode_envoi','GSM_tiers','Publimailing')
     AND NOT (L.OLDVALUE = 'NULL' AND L.NEWVALUE IS NULL)  -- On ignore les erreurs dimport
     AND L.CREATED between to_date('01/09/2010','DD/MM/YYYY')
                       AND to_date('01/10/2010','DD/MM/YYYY')
GROUP BY DATEPART('ww',l.created),
         case UUSER.VALUE when '000' then 1 else 0 end
)
  ,  Cal as
(
    select datepart('ww', to_date('01/09/2010','DD/MM/YYYY')) + level - 1 as sem
      from dual
connect by level - 1 <= datepart('ww', to_date('01/10/2010','DD/MM/YYYY')) - datepart('ww', to_date('01/09/2010','DD/MM/YYYY'))
)
  select c.sem,
         max(case r.user_siege when 0 then r.nbre_modifs else 0 end) as NBRE_MODIFS,
         max(case r.user_siege when 1 then r.nbre_modifs else 0 end) as NBRE_MODIFS_SIEGE
    from Cal c
         left outer join Req r
           on r.sem = c.sem
group by c.sem
order by c.sem asc;

09/11/2010, 12h11
Zenji

Comprenons-nous bien, ma réponse ne se voulait pas agressive. C’est juste que j’ai deux fois plus de travaille sur une semaine deux fois plus courte. J’ai simplement répondu vite.

Et en voyant une fonction datepart toute prête à être installée sur Oracle — que je n’utilise que depuis peu —, je m’en suis servi.

Merci à toi ; je teste ça et reviens vers toi.
09/11/2010, 12h28
Zenji

Citation:

ORA-12704: non-concordance de jeux de caractères
12704. 00000 - "character set mismatch"
*Cause: One of the following
- The string operands(other than an nlsparams argument) to an
operator or built-in function do not have the same character
set.
- An nlsparams operand is not in the database character set.
- String data with character set other than the database character
set is passed to a built-in function not expecting it.
- The second argument to CHR() or CSCONVERT() is not CHAR_CS or
NCHAR_CS.
- A string expression in the VALUES clause of an INSERT statement,
or the SET clause of an UPDATE statement, does not have the
same character set as the column into which the value would
be inserted.
- A value provided in a DEFAULT clause when creating a table does
not have the same character set as declared for the column.
- An argument to a PL/SQL function does not conform to the
character set requirements of the corresponding parameter.
*Action:
Erreur à la ligne 26, colonne 27 (cast sur les dates)

Une idée… J’ai déjà eu ce problème, mais jamais avec cette structure.

Je ne sais pas si ça va être exactement ce que vous attendez, mais qu'est-ce que ça donne en utilisant la fonction Oracle ?
Si vous avez la même erreur, ça ne vient pas de la fonction.

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
31
32
33
34
35
36
37
38
39
40
41
42
43
WITH Req AS
(
  SELECT to_number(to_char(l.created, 'iw')) AS sem,
         case UUSER.VALUE when '000' then 1 else 0 end AS user_siege, 
         count(DISTINCT M.C_BPARTNER_ID) AS NBRE_MODIFS
    FROM AD_CHANGELOG L
         INNER JOIN AD_TABLE T
           ON T.AD_TABLE_ID = L.AD_TABLE_ID
         INNER JOIN AD_COLUMN C
           ON C.AD_COLUMN_ID = L.AD_COLUMN_ID
          -- Auteur de la modification
         INNER JOIN AD_USER U
           ON U.AD_USER_ID = L.CREATEDBY
         INNER JOIN AD_ORG UUSER
           ON UUSER.AD_ORG_ID = U.AD_ORG_ID
          -- Membre modifié
         LEFT OUTER JOIN C_BPARTNER M
           ON M.C_BPARTNER_ID = L.RECORD_ID
         LEFT OUTER JOIN AD_ORG UT
           ON UT.AD_ORG_ID = M.AD_ORG_ID
   WHERE ut.ad_org_id = '1000878'
     AND T.TABLENAME = 'C_BPartner'                        -- On ne prend que les changements à la fiche Tiers
     AND C.COLUMNNAME IN ('IsActive','Sexe','Date_Naiss','Z_Fonction_ID','Email_tiers','Name','Assurance','M_Warehouse_ID','Tel_tiers','CurrentMembre','Handicap','Name2','Membre','Mode_envoi','GSM_tiers','Publimailing')
     AND NOT (L.OLDVALUE = 'NULL' AND L.NEWVALUE IS NULL)  -- On ignore les erreurs dimport
     AND L.CREATED BETWEEN to_date('01/09/2010','DD/MM/YYYY')
                       AND to_date('01/10/2010','DD/MM/YYYY')
GROUP BY to_number(to_char(l.created, 'iw')),
         case UUSER.VALUE when '000' then 1 else 0 end
)
  ,  Cal AS
(
    SELECT to_number(to_char(to_date('01/09/2010','DD/MM/YYYY'), 'iw')) + level - 1 AS sem
      FROM dual
connect BY level - 1 <= to_number(to_char(to_date('01/10/2010','DD/MM/YYYY'), 'iw')) - to_number(to_char(to_date('01/09/2010','DD/MM/YYYY'), 'iw'))
)
  SELECT c.sem,
         max(case r.user_siege when 0 then r.nbre_modifs else 0 end) AS NBRE_MODIFS,
         max(case r.user_siege when 1 then r.nbre_modifs else 0 end) AS NBRE_MODIFS_SIEGE
    FROM Cal c
         LEFT OUTER JOIN Req r
           ON r.sem = c.sem
GROUP BY c.sem
ORDER BY c.sem ASC;

09/11/2010, 13h04
Zenji

Reste le même problème.

to_date('01/09/2010','DD/MM/YYYY') semble ne pas plaire. Étrange, j’ai régler la même erreur en faisant ça la dernière fois justement. O_o
09/11/2010, 13h06
Waldar

Quel est le type de la colonne AD_CHANGELOG.CREATED ?

Sinon essayez simplement :

Code:

select to_date('01/09/2010','DD/MM/YYYY') as dt from dual;
09/11/2010, 13h46
Zenji

Un type DATE, rempli automatiquement avec sysdate à la création et le code que tu m’a proposé fonctionne… je dois donc cherché ailleurs. Une espace insécable glissé en copiant-collant pourrait en être la cause ?

Ou effectivement, ça pourrait être un problème.

Essayez juste la partie Cal du with :

Code:

1
2
3
4
5
6
7
8
WITH Cal AS
(
    SELECT to_number(to_char(to_date('01/09/2010','DD/MM/YYYY'), 'iw')) + level - 1 AS sem
      FROM dual
connect BY level - 1 <= to_number(to_char(to_date('01/10/2010','DD/MM/YYYY'), 'iw')) - to_number(to_char(to_date('01/09/2010','DD/MM/YYYY'), 'iw'))
)
select sem
  from Cal;

Si ça fonctionne, essayez avec votre fonction :

Code:

1
2
3
4
5
6
7
8
WITH Cal AS
(
    SELECT datepart('ww', to_date('01/09/2010','DD/MM/YYYY')) + level - 1 AS sem
      FROM dual
connect BY level - 1 <= datepart('ww', to_date('01/10/2010','DD/MM/YYYY')) - datepart('ww', to_date('01/09/2010','DD/MM/YYYY'))
)
select sem
  from Cal;

Fonctionne… c’est une erreur un peu bizarre ; je dois dire que je l’ai eu une ou deux fois sans vraiment pouvoir expliquer en quoi la solution était efficace ?!:?

Bien, j’ai juste ajouté
Code:

1 2 3 4 5 right outer join ( SELECT DATEPART('ww', to_date(to_char($P{DEPUIS_DATE},'DD/MM/YYYY'),'DD/MM/YYYY')) + level - 1 AS sem FROM dual connect BY level - 1 <= datepart('ww', to_date(to_char($P{JUSQUE_DATE},'DD/MM/YYYY'),'DD/MM/YYYY'))- datepart('ww', to_date(to_char($P{DEPUIS_DATE},'DD/MM/YYYY'),'DD/MM/YYYY'))) tabC on tabC.sem=tabA.sem
dans ma requête d’origine — parce qu’il me faut absolument avancer, et que cette solution apporte le résultat attendu —, mais je reviendrai plus tard sur votre solution.

Merci beaucoup !