Too many joins? ORA-01792: maximum number of columns in a table or view is 1000

I have a working query that fetches related data of a record from attached tables (Oracle 9i):

SELECT
  MCLM_ID, MCLM_C4C_ID, MCLM_CLM_ID, MCLM_FRSTDT_OF_SRVC, MCLM_LAS_DT_OF_SRVC,
  MCLM_AMNTCRGD, MCLM_AMNTPAID, MCLM_AMNTALWD, MCLM_AMNTDDCT, MCLM_AMNTCPAY,
  MBUR.MBUR_COB, ICD9DX1.IC9D_ICD9DGESCODE, ICD9DX1.IC9D_LONGDSCN,   ICD9DX2.IC9D_ICD9DGESCODE, ICD9DX2.IC9D_LONGDSCN, ICD9DX3.IC9D_ICD9DGESCODE,
  ICD9DX3.IC9D_LONGDSCN, HTCD.HTCD_HCCSCPT_CODE, HTCD.HTCD_CPT_DSCN, MCLM_PLCEOF_SRVCCODE,
  SENG.USR_FRSTNAM, SENG.USR_MDLENAM, SENG.USR_LAS_NAM, MCLM_SENGPVDR,   RFNG.USR_FRSTNAM, RFNG.USR_MDLENAM, RFNG.USR_LAS_NAM, MCLM_RFNGPVDR FROM
  T_MDCLCLM
INNER JOIN
  T_USR MBUR ON MCLM_PAT_ID = MBUR.MBUR_ID INNER JOIN
  T_SRVC HTCD ON MCLM_CPT4CODE = HTCD.HTCD_ID INNER JOIN
  T_USR SENG ON MCLM_SENGPVDR = SENG.PRV_ID INNER JOIN
  T_DGIS ICD9DX1 ON MCLM_ICD9DX1 = ICD9DX1.IC9D_ID LEFT OUTER JOIN
  T_DGIS ICD9DX2 ON MCLM_ICD9DX2 = ICD9DX2.IC9D_ID LEFT OUTER JOIN
  T_DGIS ICD9DX3 ON MCLM_ICD9DX3 = ICD9DX3.IC9D_ID LEFT OUTER JOIN
  T_USR RFNG ON MCLM_RFNGPVDR = RFNG.PRV_ID ORDER BY
  MCLM_FRSTDT_OF_SRVC After some modifications of the query I get the 'ORA-01792: maximum number of columns in a table or view is 1000' error. I added three more joins and replaced some columns with columns from the joined tables:

SELECT
  MCLM_ID, MCLM_C4C_ID, MCLM_CLM_ID, MCLM_FRSTDT_OF_SRVC, MCLM_LAS_DT_OF_SRVC,
  MCLM_AMNTCRGD, MCLM_AMNTPAID, MCLM_AMNTALWD, MCLM_AMNTDDCT, MCLM_AMNTCPAY,
–- User merge.
  MBUR_MSTR.MBUR_COB,
--

  ICD9DX1.IC9D_ICD9DGESCODE, ICD9DX1.IC9D_LONGDSCN,   ICD9DX2.IC9D_ICD9DGESCODE, ICD9DX2.IC9D_LONGDSCN, ICD9DX3.IC9D_ICD9DGESCODE,
  ICD9DX3.IC9D_LONGDSCN, HTCD.HTCD_HCCSCPT_CODE, HTCD.HTCD_CPT_DSCN, MCLM_PLCEOF_SRVCCODE,
–- User merge.
  SENG_MSTR.USR_FRSTNAM, SENG_MSTR.USR_MDLENAM, SENG_MSTR.USR_LAS_NAM, MCLM_SENGPVDR,
  RFNG_MSTR.USR_FRSTNAM, RFNG_MSTR.USR_MDLENAM, RFNG_MSTR.USR_LAS_NAM, MCLM_RFNGPVDR
--

FROM
  T_MDCLCLM
INNER JOIN
  T_USR MBUR ON MCLM_PAT_ID = MBUR.MBUR_ID INNER JOIN
  T_SRVC HTCD ON MCLM_CPT4CODE = HTCD.HTCD_ID INNER JOIN
  T_USR SENG ON MCLM_SENGPVDR = SENG.PRV_ID INNER JOIN
  T_DGIS ICD9DX1 ON MCLM_ICD9DX1 = ICD9DX1.IC9D_ID LEFT OUTER JOIN
  T_DGIS ICD9DX2 ON MCLM_ICD9DX2 = ICD9DX2.IC9D_ID LEFT OUTER JOIN
  T_DGIS ICD9DX3 ON MCLM_ICD9DX3 = ICD9DX3.IC9D_ID LEFT OUTER JOIN
  T_USR RFNG ON MCLM_RFNGPVDR = RFNG.PRV_ID –- User merge.
LEFT OUTER JOIN
  T_USR MBUR_MSTR ON NVL(MBUR.USR_MRGEMSTR, MBUR.USR_ID) = MBUR_MSTR.USR_ID
LEFT OUTER JOIN
  T_USR SENG_MSTR ON NVL(SENG.USR_MRGEMSTR, SENG.USR_ID) = SENG_MSTR.USR_ID
LEFT OUTER JOIN
  T_USR RFNG_MSTR ON NVL(RFNG.USR_MRGEMSTR, RFNG.USR_ID) = RFNG_MSTR.USR_ID
--

ORDER BY
  MCLM_FRSTDT_OF_SRVC I assume that the sum of the column count of all tables included in the query exceeds the 1000 limit. But, is there a way to workaround this problem?

Thanks,

Kurta Received on Mon Apr 05 2004 - 11:04:38 CDT