This discussion is archived
4 Replies Latest reply: Sep 10, 2011 7:41 PM by 672680 RSS

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

672680 Pro
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points