DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-22818: subquery expressions not allowed here

  1. #1
    Join Date
    Jun 2003
    Location
    India
    Posts
    24

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2003
    Location
    India
    Posts
    24
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2003
    Location
    India
    Posts
    24
    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
  •  


Click Here to Expand Forum to Full Width