TEMA : Error subquery materialized view - Foro de Tu Informática Fácil

Hola a todos!

Estoy intentando crear una vista materializada pero me están apareciendo errores por intentar crearla con subqueries. He visto en la doc de oracle que las subqueries no son posibles si están dentro de la sentencia SELECT pero que si están en el FROM o el WHERE si que es posible usarlas. Aqui os dejo la vista para que le deis un vistazo y ver si podeis ayudarme. Os lo agradecería mucho.

CREATE MATERIALIZED VIEW LOG ON "subscriber" WITH SEQUENCE,
ROWID
("id", "status", "id_service")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON "subscriber_events" WITH
SEQUENCE, ROWID
("created_at", "id_event", "billed", "percent_billed", "id_service")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON "subscriber_status" WITH
SEQUENCE, ROWID
("id_status")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON "service" WITH SEQUENCE, ROWID
("id", "price", "revenue")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "bill_arpu_month_by_service"
TABLESPACE plat_dat
BUILD IMMEDIATE
REFRESH FORCE
START WITH sysdate NEXT +1 MONTH
ENABLE QUERY REWRITE
AS
SELECT * FROM (SELECT created, service, billed, global_user_actives, optin, optout, new_users, dif, global_user_actives - SUM(new_users) OVER (PARTITION BY service ORDER BY created DESC) as actives_S_M, global_user_actives - SUM(new_users) OVER (PARTITION BY service ORDER BY created DESC) + new_users as actives_E_M, round((CASE WHEN (global_user_actives - SUM(new_users) OVER (PARTITION BY service ORDER BY created DESC) + new_users) =0 THEN 0 ELSE (billed/(global_user_actives - SUM(new_users) OVER (PARTITION BY service ORDER BY created DESC) + new_users)) * service_mult END),2) arpu
FROM
( select to_char("created_at", 'yyyymm') "CREATED", AVG("service"."id") service, SUM( CASE WHEN "id_event" IN ('1', '5', '3') and "billed" = '1' THEN 1 WHEN "id_event" IN ('6', '4') and "billed" = '1' THEN "percent_billed"/100 ELSE 0 END) AS BILLED, (select count("id") from "subscriber" join "subscriber_status" on "subscriber"."status" = "subscriber_status"."id_status" where "subscriber"."status" = 1 and "subscriber"."id_service" = "service"."id") as global_user_actives, SUM( CASE WHEN "id_event" IN ('1') THEN 1 ELSE 0 END) AS optin, SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END) AS optout, (SUM( CASE WHEN "id_event" IN ('1','3') THEN 1 ELSE 0 END) - SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END)) as new_users, ((select count("id") from "subscriber" join "subscriber_status" on "subscriber"."status" = "subscriber_status"."id_status" where "subscriber"."status" = 1 and "subscriber"."id_service" = "service"."id") - (SUM( CASE WHEN "id_event" IN ('1','3') THEN 1 ELSE 0 END) - SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END))) as dif,
(COALESCE( AVG("service"."price")*AVG("service"."revenue")/100 , 0)) as service_mult
from "subscriber_events" JOIN "service" ON "subscriber_events"."id_service" = "service"."id" where "id_event" IN ('1', '2', '3', '4', '5', '6') group by "service"."id", to_char("created_at", 'yyyymm') order by "service"."id", "CREATED" DESC )
ORDER BY "SERVICE", "CREATED" DESC);

saludos!!