CODEWITHSUNDEEP
sqloracle-database
Pooja Patil
Pooja Patil

Reputation: 21

ORA-01792: maximum number of columns in a table or view is 1000 error while using WITH in sql

I have a query :

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

Upvotes: 1

Views: 24639

Answers (1)

Jim Wartnick
Jim Wartnick

Reputation: 2196

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

Creating view generates ORA-01792 maximum number of columns in a table or view is 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

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

Bug 19893041 : ORA-01792 HAPPEN WHEN UPDATE TO 12.1.0.2

closed as dup of

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

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.

Upvotes: 9

Related Questions