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 vistapara 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" WITHSEQUENCE, ROWID
("created_at", "id_event", "billed", "percent_billed", "id_service")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON "subscriber_status" WITHSEQUENCE, 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 BYservice 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);
22818. 00000 - "subquery expressions not allowed here"
*Cause: An attempt was made to use a subquery expression where these
are not supported.
- Versión para impresión
- Inicie sesión o registrese para enviar comentarios
Mario, sólo le he echado un
Subido por Carlos el 28 Noviembre, 2013 - 22:58
Hola Carlos! He probado
Subido por mariomario89 el 4 Diciembre, 2013 - 16:55
En respuesta a Mario, sólo le he echado un por Carlos
Hola Carlos!
He probado tambien como tu has dicho (la verdad que no me acuerdo porque puse ese SELECT * FROM XD) ... y tampoco me ha funcionado.
Al final lo que he hecho ha sido crear una vista y apartir de ahi crear la vista materializada y por lo visto me ha funcionado. Lo unico que estoy buscando por internet opciones o ejemplos de vistas materializadas basadas en vistas pero no encuentro nada.
Esto ha sido lo que he hecho.
CREATE OR REPLACE VIEW "bill_arpu_month_view" as
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 ;
CREATE MATERIALIZED VIEW "bill_arpu_month_by_service"
TABLESPACE plat_dat
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE
AS
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
"bill_arpu_month_view"