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:
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 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
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
Partager