ORA-22818: subquery expressions not allowed here

[kanna]

Hi all
suggestion required.
iam getting this error. how to handle this or write this query.

SQL> CREATE MATERIALIZED VIEW MV_TMP_CALENDAR_DAYS AS
SELECT B.PORTFOLIO_GROUP_ID,
A.CAL_DAY_DT,
TRIM(A.CAL_MONTH_STR) REPORT_PERIOD,
TO_CHAR(CAL_DAY_DT, 'DD') -
(SELECT COUNT(*) FROM SOURCE_SYSTEM_HOLIDAYS B
WHERE TO_CHAR(A.CAL_DAY_DT, 'YYYYMM') = TO_CHAR(B.CAL_DATE, 'YYYYMM')
AND B.CAL_DATE <= A.CAL_DAY_DT) CURRENT_WORK_DAYS_NUM,
TO_CHAR(LAST_DAY(CAL_DAY_DT),'DD') -
(SELECT COUNT(*) FROM SOURCE_SYSTEM_HOLIDAYS B
WHERE TO_CHAR(A.CAL_DAY_DT, 'YYYYMM') = TO_CHAR(B.CAL_DATE, 'YYYYMM')) ALL_WORK_DAYS_NUM
FROM CCRT_CALENDAR_DIM A,
PORTFOLIO_GROUPS B;
(SELECT COUNT(*) FROM SOURCE_SYSTEM_HOLIDAYS B
*
ERROR at line 7:
ORA-22818: subquery expressions not allowed here

Thnx,

[slimdave]

you will have to rewrite is as an outer join fom A and B to the subquery tables.

Also, rewrite ...

WHERE TO_CHAR(CAL_DAY_DT, 'YYYYMM') = TO_CHAR(CAL_DATE, 'YYYYMM')

... as ...

WHERE
CAL_DATE >= TRUNC(CAL_DAT_DT,'MM')
and
CAL_DATE < ADD_MONTHS(TRUNC(CAL_DAT_DT,'MM'),1)

[kanna]

Hi Thnx Slimdave,
Just need one more help

Iam not clear... and i don't know how to phrase the OUTER JOIN query.
can draft the query for me.
Thnx again

[slimdave]

Instead of ...

[code]
select
a.col1,
(
Select
b.col2
from
b
where
b.col1 = a.col1
) col2
from
a
[\code]

... you would write ...

[code]
select
a.col1,
b.col2
from
a,
b
where
a.col1 = b.col1(+)
[\code]

It ios the "(+)" that makes this an outer join, and causes all rows from a to be returned regardless of whether there is a corresponding value of col1 in table b. If there is no corresponding value, the second column of the query result will be null.

[kanna]

Thnx slimdave
I will do this and revert

thnx again