Discussion :

[Heremion]

Bonjour à tous,

Voilà je me tourne encore une fois vers vous et vos neurones car depuis plusieurs heures, je creuse je creuse mais ne trouve pas solution à mon problème.

J'ai une requête initiale qui me retourne pour chaque application, le nombre de cas de test groupé sur l'application et le statut.

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
select RCYC_NAME,
       REL_NAME as "Release",
       TC_STATUS,
       count(*)
FROM   REQ
    FULL JOIN REQ_COVER
         ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
    FULL JOIN REQ_RELEASES
         ON REQ_RELEASES.RQRL_REQ_ID =  REQ.RQ_REQ_ID
    FULL JOIN RELEASES
         ON RELEASES.REL_ID =  REQ_RELEASES.RQRL_RELEASE_ID
    FULL JOIN TEST
         ON  TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
    INNER JOIN TESTCYCL
         ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
    INNER JOIN RELEASE_CYCLES
         ON TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
    INNER JOIN CYCLE
         ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
WHERE (RCYC_NAME LIKE @Filtre_RELEASE@ escape '\')
    AND RQ_TYPE_ID <> 1
    AND RCYC_PARENT_ID = RELEASES.REL_ID
    AND CY_CYCLE NOT LIKE '%ecevabilit%'
    AND CY_CYCLE NOT LIKE '%onformit%'
    AND CY_CYCLE NOT LIKE '%nstallation%'
    AND ( (@Filtre_Testeur@ = 1)
            AND (TC_ACTUAL_TESTER IN (...) or TC_ACTUAL_TESTER is NULL)
        )
group by RCYC_NAME, REL_NAME, TC_STATUS
ORDER BY REL_NAME

J'obtiens alors ce rendu :



Mais du coup, pour une même application, j'ai plusieurs lignes.

Ce que je souhaiterais avoir, c'est pour une même application, avoir trois colonnes "Passed", "Failed", "NoRun" par exemple et avoir le count(*) dans chacun.

J'ai tenté une approche dans ce genre mais ça ne fonctionne pas :

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
select RCYC_NAME,
       REL_NAME as "Release",
       TC_STATUS,
       null as "Failed",
       null as "NoRun",
       count(*) as "Passed"
FROM   REQ
    FULL JOIN REQ_COVER
         ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
    FULL JOIN REQ_RELEASES
         ON REQ_RELEASES.RQRL_REQ_ID =  REQ.RQ_REQ_ID
    FULL JOIN RELEASES
         ON RELEASES.REL_ID =  REQ_RELEASES.RQRL_RELEASE_ID
    FULL JOIN TEST
         ON  TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
    INNER JOIN TESTCYCL
         ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
    INNER JOIN RELEASE_CYCLES
         ON TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
    INNER JOIN CYCLE
         ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
WHERE (RCYC_NAME LIKE @Filtre_RELEASE@ escape '\')
    AND RQ_TYPE_ID <> 1
    AND RCYC_PARENT_ID = RELEASES.REL_ID
    AND CY_CYCLE NOT LIKE '%ecevabilit%'
    AND CY_CYCLE NOT LIKE '%onformit%'
    AND CY_CYCLE NOT LIKE '%nstallation%'
    AND ( (@Filtre_Testeur@ = 1)
            AND (TC_ACTUAL_TESTER IN (...) or TC_ACTUAL_TESTER is NULL)
        )
    AND TC_STATUS IN ('Passed','N/A')
group by RCYC_NAME, REL_NAME, TC_STATUS
ORDER BY REL_NAME

UNION ALL

select RCYC_NAME,
       REL_NAME as "Release",
       TC_STATUS,
       count(*) as "Failed",
       null as "NoRun",
       null as "Passed"
FROM   REQ
    FULL JOIN REQ_COVER
         ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
    FULL JOIN REQ_RELEASES
         ON REQ_RELEASES.RQRL_REQ_ID =  REQ.RQ_REQ_ID
    FULL JOIN RELEASES
         ON RELEASES.REL_ID =  REQ_RELEASES.RQRL_RELEASE_ID
    FULL JOIN TEST
         ON  TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
    INNER JOIN TESTCYCL
         ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
    INNER JOIN RELEASE_CYCLES
         ON TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
    INNER JOIN CYCLE
         ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
WHERE (RCYC_NAME LIKE @Filtre_RELEASE@ escape '\')
    AND RQ_TYPE_ID <> 1
    AND RCYC_PARENT_ID = RELEASES.REL_ID
    AND CY_CYCLE NOT LIKE '%ecevabilit%'
    AND CY_CYCLE NOT LIKE '%onformit%'
    AND CY_CYCLE NOT LIKE '%nstallation%'
    AND ( (@Filtre_Testeur@ = 1)
            AND (TC_ACTUAL_TESTER IN (...) or TC_ACTUAL_TESTER is NULL)
        )
    AND TC_STATUS IN ('Failed')
group by RCYC_NAME, REL_NAME, TC_STATUS
ORDER BY REL_NAME

UNION ALL

select RCYC_NAME,
       REL_NAME as "Release",
       TC_STATUS,
       null as "Failed",
       count(*) as "NoRun",
       null as "Passed"
FROM   REQ
    FULL JOIN REQ_COVER
         ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
    FULL JOIN REQ_RELEASES
         ON REQ_RELEASES.RQRL_REQ_ID =  REQ.RQ_REQ_ID
    FULL JOIN RELEASES
         ON RELEASES.REL_ID =  REQ_RELEASES.RQRL_RELEASE_ID
    FULL JOIN TEST
         ON  TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
    INNER JOIN TESTCYCL
         ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
    INNER JOIN RELEASE_CYCLES
         ON TESTCYCL.TC_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
    INNER JOIN CYCLE
         ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
WHERE (RCYC_NAME LIKE @Filtre_RELEASE@ escape '\')
    AND RQ_TYPE_ID <> 1
    AND RCYC_PARENT_ID = RELEASES.REL_ID
    AND CY_CYCLE NOT LIKE '%ecevabilit%'
    AND CY_CYCLE NOT LIKE '%onformit%'
    AND CY_CYCLE NOT LIKE '%nstallation%'
    AND ( (@Filtre_Testeur@ = 1)
            AND (TC_ACTUAL_TESTER IN (...) or TC_ACTUAL_TESTER is NULL)
        )
    AND TC_STATUS IN ('No Run')
group by RCYC_NAME, REL_NAME, TC_STATUS
ORDER BY REL_NAME

Voilà, j'ai essayé plusieurs formattages mais rien n'y fait...Peut-être auriez-vous des lumières différentes pour m'éclairer.

J'espère avoir fourni suffisamment d'informations ?

En tout cas, merci pour votre aide par avance.

Heremion

[skuatamad]

Sans repartir de votre requête, juste pour proposer une idée :

Code :

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

1
2
3
4
5
6
select appliname, release
     , sum(case when statut = 'Passed' then 1 end) as passed
     , sum(case when statut = 'Failed' then 1 end) as failed
     , sum(case when statut = 'Norun' then 1 end) as norun
  from requete_base
 group by appliname, release

[Heremion]

GREAT!

Merci beaucoup pour ton aide!

Heremion