Ora-22818

[syedmagdumpeer]

Hi All,

Iam getting an error while using this query-

ERROR-ORA-22818: subquery expressions not allowed here

QUERY:

SELECT ROUND(MONTHS_BETWEEN (
(SELECT TO_DATE('01-'||period_name,'DD-MON-RRRR') FROM gl_periods GP WHERE
TRUNC(SYSDATE) BETWEEN TRUNC(GP.START_DATE) AND TRUNC(GP.END_DATE)
AND GP.PERIOD_SET_NAME = 'AT Calendar'
AND GP.Period_NUM BETWEEN 1 AND 12
),
(SELECT DISTINCT(TO_DATE('01-'||period_name,'DD-MON-RRRR')) FROM gl_periods GP,INV.MTL_TRANSACTION_ACCOUNTS WHERE
TRUNC(INV.MTL_TRANSACTION_ACCOUNTS.TRANSACTION_DATE) BETWEEN TRUNC(GP.START_DATE) AND TRUNC(GP.END_DATE)
AND GP.PERIOD_SET_NAME = 'AT Calendar'
AND GP.Period_NUM BETWEEN 1 AND 12
)
) )



FROM DUAL

[PAVB]

The good news is function supports subquery, please see test query below...

select  round(months_between(
        (select sysdate from dual),
        (select sysdate -60 from dual)
        ))
from    dual;

You have to look into your individual subqueries and be sure they return an unique DATE; we cannot do it for you 'cause we do not have your involved tables.

Hope this helps.

[chrisrlong]

First, let's clean this up a little and see what you're actually trying to do:

SELECT 
   ROUND(
      MONTHS_BETWEEN (
         (
         SELECT 
            TO_DATE('01-'||PERIOD_NAME,'DD-MON-RRRR') 
         FROM 
            GL_PERIODS GP 
         WHERE
            TRUNC(SYSDATE)       BETWEEN  TRUNC(GP.START_DATE) 
                                 AND      TRUNC(GP.END_DATE)      AND 
            GP.PERIOD_SET_NAME   =        'AT CALENDAR'           AND 
            GP.PERIOD_NUM        BETWEEN   1 
                                 AND      12
         ), 
         (
         SELECT 
            DISTINCT(TO_DATE('01-'||PERIOD_NAME,'DD-MON-RRRR')) 
         FROM 
            GL_PERIODS                     GP,
            INV.MTL_TRANSACTION_ACCOUNTS   MTA
         WHERE
            TRUNC(MTA.TRANSACTION_DATE)   BETWEEN  TRUNC(GP.START_DATE) 
                                          AND      TRUNC(GP.END_DATE)   AND 
            GP.PERIOD_SET_NAME            =        'AT CALENDAR'        AND 
            GP.PERIOD_NUM                 BETWEEN   1 
                                          AND      12
         )
      ) 
   )
FROM
   DUAL

This leads me to several questions:

- If you need a DISTINCT, I'm guessing that multiple records are being returned without the DISTINCT. My guess is that multiple records are being returned even *with* the DISTINCT.

- Avoid BETWEEN - it leads to doing TRUNC()s, which are bad. Any column modified by a function will not use an index (except function-based indexes), so try not to do it.

- Why are you using TRUNC() on a GP.START_DATE and GP.END_DATE? Why would these values not already be date-only? There should be triggers that guarantee that these values are date-only so you don't have to TRUNC() them everywhere and screw up your indexes.

So, IF this query worked, it would be much better to write is as:

SELECT
   ROUND(
      MONTHS_BETWEEN (
         (
         SELECT
            TO_DATE('01-'||PERIOD_NAME,'DD-MON-RRRR')
         FROM
            GL_PERIODS GP
         WHERE
            GP.START_DATE        <=  SYSDATE         AND
            GP.END_DATE          >   SYSDATE - 1     AND
            GP.PERIOD_SET_NAME   =   'AT CALENDAR'   AND
            GP.PERIOD_NUM        >=  1               AND
            GP.PERIOD_NUM        <=  12
         ),
         (
         SELECT
            DISTINCT(TO_DATE('01-'||PERIOD_NAME,'DD-MON-RRRR'))
         FROM
            INV.MTL_TRANSACTION_ACCOUNTS    MTA,
            GL_PERIODS                     GP
         WHERE
            MTA.TRANSACTION_DATE   >=   GP.START_DATE             AND
               -- Yes, these 'say' the same thing, but it gives the 
               --   optimizer more choices, which is always good
            MTA.TRANSACTION_DATE   <   GP.END_DATE + 1            AND
            GP.END_DATE            >   MTA.TRANSACTION_DATE   - 1 AND
            GP.PERIOD_SET_NAME     =   'AT CALENDAR'              AND
            GP.PERIOD_NUM          >=   1                         AND
            GP.PERIOD_NUM          <=   12
         )
      )
   )
FROM
   DUAL

But you need to be more specific about what you are actually trying to do, because my guess is that the SQL is completely wrong.