Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Oracle »

Error subquery materialized view

Estas en el tema de Error subquery materialized view en el foro de Oracle en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 27/11/2013, 03:54
 
Fecha de Ingreso: abril-2013
Mensajes: 20
Antigüedad: 6 años
Puntos: 1
Error subquery materialized view

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!!

Etiquetas: query, subquery, view
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 04:48.