ORA-01792:在SQL中使用WITH时，表或视图中的最大列数为1000错误 [英] ORA-01792: maximum number of columns in a table or view is 1000 error while using WITH in sql

问题描述

我有一个查询:

WITH abc AS
(

   (SELECT SRC_DATA.*,
     (SELECT MAX(DECODE(OBJ.AUD_ACTION_FLAG,'D',OBJ.OUPDATE_COUNT,OBJ.NUPDATE_COUNT))
    FROM SMARTTRIAL_ODR_LANDING.AUD_TRIAL_DESIGN OBJ
    WHERE OBJ.AUD_DATE_CHANGED BETWEEN TO_DATE('01-JAN-1900') AND (SRC_DATA.AUD_DATE_CHANGED)
    AND DECODE(OBJ.AUD_ACTION_FLAG,'D',OBJ.OTRIAL_NO,OBJ.NTRIAL_NO)= DECODE(SRC_DATA.AUD_ACTION_FLAG,'D',SRC_DATA.OTRIAL_NO,SRC_DATA.NTRIAL_NO)
    AND OBJ.AUD_ACTION_FLAG <> 'D'
    ) UPDATE_COUNT,

 /***Multiple select statement like above with many other look up tables like  AUD_TRIAL_DESIGN ****/

FROM SMARTTRIAL_ODR_LANDING.AUD_TRIAL SRC_DATA  /***AUD_TRIAL  is the base table***/
),    


WITH def AS
(SELECT OBJ_DATA .*,
/***Similar statement as mentioned in above block and lookup table is AUD_OBJECTIVE***/

FROM SMARTTRIAL_ODR_LANDING.AUD_TRIAL_OBJECTIVE OBJ_DATA /***AUD_TRIAL_OBJECTIVE is the base table***/
)

----查询以选择列-----

----Query to select columns-----

FROM abc
LEFT JOIN def
LEFT JOIN xyz ON (column from def = column from xyz)

对于我编写的查询的类似结构，返回以下错误:

For the simliar structure of query written by me, following error is returned :

ORA-01792: maximum number of columns in a table or view is 1000
01792. 00000 -  "maximum number of columns in a table or view is 1000"
*Cause:    An attempt was made to create a table or view with more than 1000
           columns, or to add more columns to a table or view which pushes
           it over the maximum allowable limit of 1000. Note that unused
           columns in the table are counted toward the 1000 column limit.
*Action:   If the error is a result of a CREATE command, then reduce the
           number of columns in the command and resubmit. If the error is
           a result of an ALTER TABLE command, then there are two options:
           1) If the table contained unused columns, remove them by executing
           ALTER TABLE DROP UNUSED COLUMNS before adding new columns;
           2) Reduce the number of columns in the command and resubmit.

任何人都可以提出解决方案

Could anyone please suggest a solution

推荐答案

我们遇到了类似的问题(以下是SR的摘录):

We had a similar problem (Here is an excerpt from the SR):

创建视图会生成ORA-01792，一个表或视图中的最大列数为1000

Creating view generates ORA-01792 maximum number of columns in a table or view is 1000

我们有一个新的应用程序，该应用程序的视图包含35列.但是，在创建它时，它会错误地指出存在1000多个列，这是错误的.我将附加视图定义

We have a new application that has a view that contains 35 columns. However, when creating it, it errors out stating that there are over 1000 columns, which is false. I will attach the view definition

这是Oracle所说的(它确实解决了问题):

Here is what Oracle said (and it did fix the problem):

错误19893041:ORA-01792在更新为12.1.0.2时发生

Bug 19893041 : ORA-01792 HAPPEN WHEN UPDATE TO 12.1.0.2

作为

错误19509982:默认情况下无法修复ORA-1792的修复程序.

Bug 19509982 : DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT.

解决方案: SQL> alter system set"_fix_control" ='17376322:OFF'; 或者 B.应用补丁19509982 (与随附的opatch没有发现冲突)

Solution: SQL> alter system set "_fix_control"='17376322:OFF'; Or B. Apply patch 19509982 (no conflicts found with the attached opatch)

这可能与您遇到的问题相同.

That may be the same issue you're encountering.

这篇关于ORA-01792:在SQL中使用WITH时，表或视图中的最大列数为1000错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！