Hi everyone,
as the title suggests, I get the ORA-22818 error when creating a materialized view as I am trying to use a subquery.
The thing I don't explain, however, is that if I insert the same subquery in the COALESCE function, the error is no longer generated, how come?
Here are the 2 scripts, one that generates the error and the other not (I know that the proposed code could be rewritten with a join, but it is a simplified script to expose the problem):
Script with error
CREATE MATERIALIZED VIEW MV
AS
WITH
CTE_T AS (
SELECT '2020/05/05' AS DT FROM DUAL UNION ALL
SELECT '2020/05/08' AS DT FROM DUAL
)
, CTE_RACC AS (
SELECT TO_DATE('2020/05/03','YYYY/MM/DD') AS D_RACC, '3' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/04','YYYY/MM/DD') AS D_RACC, '4' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/05','YYYY/MM/DD') AS D_RACC, '5' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/06','YYYY/MM/DD') AS D_RACC, '6' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/07','YYYY/MM/DD') AS D_RACC, '7' AS I FROM DUAL
)
SELECT DISTINCT
t1.DT
, (SELECT racc.I FROM CTE_RACC racc WHERE racc.D_RACC = TO_DATE(t1.DT,'YYYY/MM/DD')) AS I
FROM CTE_T t1
;
Script OK
CREATE MATERIALIZED VIEW MV
AS
WITH
CTE_T AS (
SELECT '2020/05/05' AS DT FROM DUAL UNION ALL
SELECT '2020/05/08' AS DT FROM DUAL
)
, CTE_RACC AS (
SELECT TO_DATE('2020/05/03','YYYY/MM/DD') AS D_RACC, '3' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/04','YYYY/MM/DD') AS D_RACC, '4' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/05','YYYY/MM/DD') AS D_RACC, '5' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/06','YYYY/MM/DD') AS D_RACC, '6' AS I FROM DUAL UNION ALL
SELECT TO_DATE('2020/05/07','YYYY/MM/DD') AS D_RACC, '7' AS I FROM DUAL
)
SELECT DISTINCT
t1.DT
, COALESCE(
(SELECT racc.I FROM CTE_RACC racc WHERE racc.D_RACC = TO_DATE(t1.DT,'YYYY/MM/DD'))
, '0'
) AS I
FROM CTE_T t1
;
Thanks in advance