SELECT RCC.campagne_id,
decode(rcc.aggregate_done,1,nvl(rcc.nb_opened,0), (SELECT /*+ index(RO idx_ro_camp_url_member) */ COUNT(DISTINCT RO.member_id) FROM report_offre RO WHERE RO.campagne_id = RCC.campagne_id AN
D RO.url_id = -1)) opened,
decode(rcc.aggregate_done,1,nvl(rcc.nb_response,0), (SELECT /*+ index(RO idx_ro_camp_url_member) */ COUNT(DISTINCT RO.member_id) FROM report_offre RO WHERE RO.campagne_id = RCC.campagne_id A
ND RO.url_id IN (SELECT /*+ index(U idx_url_mes_ordre) */ U.url_id FROM url U WHERE U.TYPE not in (4,10) AND U.message_id=RCC.message_id))) click_through,
(RCC.nb_message - (RCC.nb_erreur + RCC.nb_soft_error)) pushed,
decode(rcc.aggregate_done,1,nvl(rcc.nb_total_click,0), (SELECT /*+ index(RO idx_ro_camp_url_member) */ COUNT(RO.member_id) FROM report_offre RO WHERE RO.campagne_id = RCC.campagne_id AND RO.
url_id IN (SELECT /*+ index(U idx_url_mes_ordre) */ U.url_id FROM url U WHERE U.TYPE not in (4,10) AND U.message_id = RCC.message_id))) total_click,
decode(rcc.aggregate_done,1,nvl(rcc.nb_unjoined,0) , (SELECT /*+ index(RO idx_ro_camp_url_member) */ COUNT(DISTINCT RO.member_id) FROM report_offre RO WHERE RO.campagne_id = RCC.campagne_id
AND (RO.url_id = 0 OR RO.url_id IN (SELECT /*+ index(U idx_url_mes_ordre) */ U.url_id FROM url U WHERE U.TYPE in (4,10) AND U.message_id = RCC.message_id)))) unjoined
FROM (SELECT /*+ index (C idx_campagne_man_date) */ c.aggregate_done, RC.nb_opened,RC.nb_response,RC.nb_unjoined,RC.nb_conversion ,RC.nb_total_click, RC.campagne_id, RC.nb_message, RC.nb_er
reur, RC.nb_soft_error, RC.nb_skipped, C.message_id FROM campagne C, report_campagne RC WHERE c.client_id = '||to_char(clientId)||' AND C.campagne_id IN '||listCamp||' AND RC.campagne_id = C
.campagne_id ORDER BY RC.campagne_id) RCC';