Bonjour,

Je voulais changer la requête initiale en enlevant le WITH mais cela me donne des valeurs erronées.

Merci de bien vouloir m'aider.

La requête initiale:

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
WITH TRAFI AS (SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC  FROM TRAFIC
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC = 'Voice' 
AND DIRC_APPL = 'OUT'
GROUP BY ID_DATE),
TRAFIC_OP AS (SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC_OPERATEUR FROM TRAFIC_OPERATEUR
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC <> 'SMS'
AND DIRC_APPL <> 'IN'
GROUP BY ID_DATE),
TICKETS_INTER AS (SELECT ID_DATE, SUM(COUT_HT) AS COUT_TICKETS_INTERCO FROM TICKETS_INTERCO
WHERE ID_DATE  between '21/12/2007' and '27/12/2007'
AND CALL_DIRC = 'OUT'
AND CALL_TYP = 'Voice'
AND CALL_TYP2 NOT IN ('1','2','3')
GROUP BY ID_DATE)
SELECT TRAFI.ID_DATE, TRAFI.COUT_TRAFIC, TRAFIC_OP.COUT_TRAFIC_OPERATEUR, TICKETS_INTER.COUT_TICKETS_INTERCO
FROM TRAFI, TRAFIC_OP, TICKETS_INTER
WHERE TRAFI.ID_DATE = TRAFIC_OP.ID_DATE
AND TRAFI.ID_DATE = TICKETS_INTER.ID_DATE
Solution1:

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
select T.id_date, sum(T.COUT_INTR), sum(TP.cout_intr), sum(TI.cout_ht)
from trafic T, 
( 
select cout_intr 
from trafic_operateur
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC <> 'SMS'
AND DIRC_APPL <> 'IN'
) TP,
(
select cout_ht
from tickets_interco
WHERE ID_DATE  between '21/12/2007' and '27/12/2007'
AND CALL_DIRC = 'OUT'
AND CALL_TYP = 'Voice'
AND CALL_TYP2 NOT IN ('1','2','3')
) TI
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC = 'Voice' 
AND DIRC_APPL = 'OUT'
group by T.id_date

Solution2:


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
select T.id_date, sum(T.COUT_INTR), sum(TP.cout_intr), sum(TI.cout_ht)
from trafic T, trafic_operateur TP, tickets_interco TI
where T.id_date = TP.id_date
and  T.id_date = TI.id_date
AND T.TYP_TRFC = 'Voice' 
AND T.DIRC_APPL = 'OUT'
AND TI.CALL_DIRC = 'OUT'
AND TI.CALL_TYP = 'Voice'
AND TI.CALL_TYP2 NOT IN ('1','2','3')
AND TP.TYP_TRFC <> 'SMS'
AND TP.DIRC_APPL <> 'IN'
group by T.id_date

Solution3:


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
select T.id_date, sum(T.COUT_INTR), sum(TP.cout_intr), sum(TI.cout_ht)
from trafic T, trafic_operateur TP, tickets_interco TI
where T.id_date = TP.id_date
and  T.id_date = TI.id_date
and T.ID_DATE between '21/12/2007' and '27/12/2007'
AND T.TYP_TRFC = 'Voice' 
AND T.DIRC_APPL = 'OUT'
AND TI.CALL_DIRC = 'OUT'
AND TI.CALL_TYP = 'Voice'
AND TI.CALL_TYP2 NOT IN ('1','2','3')
AND TP.TYP_TRFC <> 'SMS'
AND TP.DIRC_APPL <> 'IN'
group by T.id_date