4 Replies Latest reply: Sep 10, 2011 9:41 PM by 672680 RSS

    ORA-01788: CONNECT BY clause required in this query block

    672680
      My Oracle version is: 10.2.0.4.0 (for both DEV and PROD)

      Below is the SQL which throws error ORA-01788 on PROD during the execution. I can guarantee that the syntax is right.
      Also, the odd thing about this is that, it works fine on the DEV which is the same version as the PROD.

      So, could anyone help explain why? Is it a oracle bug?
      is there any easy workaround for this?

      Thanks in advance
      SELECT  *
      FROM
        DI_CODE_LEVEL,
        ( SELECT D.TYPE_CODE,D.CODE,D.CODE_DESC FROM DI_CODE_LEVEL D WHERE D.TYPE_CODE='KHLX-V'
        )  KHLX,
        DM_D_LO_CUST,
        DM_D_ALL_ORG,
        DM_D_DATE,
        DM_F_LO_HTZXFX,
        DM_D_AREA_CONFIG
      WHERE
        ( DM_F_LO_HTZXFX.ORG_ID=DM_D_ALL_ORG.ORG_ID 
       AND DM_D_ALL_ORG.FROM_DT<=to_date(20100225,'YYYYMMDD') 
       and DM_D_ALL_ORG.TO_DT>to_date(20100225,'YYYYMMDD') )
        AND  ( DM_F_LO_HTZXFX.CUST_ID=DM_D_LO_CUST.CUST_ID  )
        AND  ( to_date(DM_D_DATE.DAY_ID,'YYYYMMDD')=DM_F_LO_HTZXFX.CURRENT_DT  )
        AND  ( DI_CODE_LEVEL.TYPE_CODE='QY'  )
        AND  ( DM_D_LO_CUST.AREA_ID=DM_D_AREA_CONFIG.DQ_ID    
        and DM_D_AREA_CONFIG.FROM_DT<=to_date(20100225,'YYYYMMDD') 
        and DM_D_AREA_CONFIG.TO_DT>to_date(20100225,'YYYYMMDD') )
        AND  ( KHLX.CODE=DM_D_LO_CUST.CUST_TYPE  )
        AND  ( DM_D_ALL_ORG.S_SYSTEM='EAS-LO'  )
        AND  ( DI_CODE_LEVEL.CODE=DM_D_AREA_CONFIG.QY_ID  )
       ---problematic part starts
        AND  ( DM_D_ALL_ORG.ORG_ID in 
             ( select distinct org_id from DM_D_ALL_ORG 
                where (S_SYSTEM='EAS-LO')start with org_id in  
                    (select org_id from T_USER_ORG_MAPPING where upper(user_id)= upper('Administrator') )  
               CONNECT BY  UP_ORG_ID=PRIOR ORG_ID )  ) 
       ---ends
        AND  DM_D_ALL_ORG.ORG_ID  =  '12110000'
         AND  (DI_CODE_LEVEL.CODE  =  '100000'  OR  DI_CODE_LEVEL.UP_CODE  =  '100000' )
         AND  ( DM_D_DATE.DAY_ID=20100225  );
      PS: the code is created by program, so please bear with the ugliness.
      Thanks