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);
Error SQL: ORA-22818: expresiones de subconsulta no permitidas aquí
22818. 00000 - "subquery expressions not allowed here"
*Cause: An attempt was made to use a subquery expression where these
are not supported.
*Action: Rewrite the statement without the subquery expression.
saludos!!
