-
ORA-22818: subquery expressions not allowed here
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,
Luv
Kanna
-
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)
-
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
Luv
Kanna
-
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.
-
Thnx slimdave
I will do this and revert
thnx again
Luv
Kanna
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|