Hi,
I am trying to create a materialized view from the below query but I am getting an error subquery not allowed. How should I format the query to yield same results?

CREATE MATERIALIZED VIEW PGALEGO.EQT_SIM_CHANGES_MV
TABLESPACE EDWH_MICA_DATA
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('29-Jan-2016 15:42:09','dd-mon-yyyy hh24:mi:ss')
NEXT sysdate+240/1440
WITH PRIMARY KEY
AS
SELECT TO_TIMESTAMP (
SUBSTR (created_on, 9, 2)
|| '-'
|| SUBSTR (created_on, 5, 3)
|| '-'
|| SUBSTR (created_on, -4)
|| ''
|| SUBSTR (CREATED_ON, 11, 9),
'DD-MM-YYYY HH24:MI:SS'
--+ INTERVAL '1' HOUR
) "CREATION_DATE",
(SELECT name
FROM xx_branches_bss
WHERE id IN
(SELECT branch_id
FROM xx_agents_bss
WHERE id IN
(SELECT created_by
FROM NOTIFICATIONS_LOGS_BSS
WHERE customer_id = a.customer_id
AND subscription_id = a.subscription_id
AND subscription_old_value = a.subscription_old_value
AND subscription_new_value = a.subscription_new_value
)
)
) "BRANCH",
(SELECT c.first_name || ' ' || c.last_name
FROM xx_customers_bss c
WHERE c.id = a.customer_id
) "CUSTOMER_NAME",
(SELECT IDENTITY_NUMBER
FROM xx_customers_bss c
WHERE c.id = a.customer_id
) "ID_NUMBER",
(SELECT TO_CHAR (sc.iccid)
FROM xx_sim_catalog_bss sc
WHERE sc.imsi = a.SUBSCRIPTION_OLD_VALUE
) "OLD_ICCID",
(SELECT sc1.TYPE
FROM xx_sim_catalog_bss sc1
WHERE sc1.imsi = a.SUBSCRIPTION_OLD_VALUE
) "OLD_TYPE",
(SELECT TO_CHAR (sc.iccid)
FROM xx_sim_catalog_bss sc
WHERE sc.imsi = a.SUBSCRIPTION_NEW_VALUE
) "NEW_ICCID",
(SELECT sc2.TYPE
FROM xx_sim_catalog_bss sc2
WHERE sc2.imsi = a.SUBSCRIPTION_NEW_VALUE
) "NEW_TYPE",
(SELECT name
FROM xx_agents_bss
WHERE id = a.created_by
) "UPDATED_BY",
(SELECT sb.msisdn
FROM xx_subscriptions_bss sb
WHERE sb.id = a.subscription_id
AND SB.CUSTOMER_ID = a.customer_id
) "MSISDN",
(SELECT sc1.vendor
FROM xx_sim_catalog_bss sc1
WHERE sc1.imsi = a.SUBSCRIPTION_OLD_VALUE
) "OLD_VENDOR",
(SELECT sc1.vendor
FROM xx_sim_catalog_bss sc1
WHERE sc1.imsi = a.SUBSCRIPTION_NEW_VALUE
) "NEW_VENDOR"
a.id EVENT_ID,
a.subscription_id SUBSCRIPTION_ID,
FROM NOTIFICATIONS_LOGS_BSS a
WHERE a.action_performed = 'ChangeSim'
;

You cannot, however, define a materialized view with a subquery in the SELECT list of the defining query

select ...
FROM   NOTIFICATIONS_LOGS_BSS a, xx_sim_catalog_bss sc1
WHERE  a.action_performed = 'ChangeSim'
and    sc1.imsi (+) = a.SUBSCRIPTION_NEW_VALUE
...