5 Replies Latest reply on Dec 21, 2016 2:28 AM by Beauty_and_dBest

    11g vs 12c

    Beauty_and_dBest

      Hi ALL,

       

      I run this sql query in 11g and it is okay, but it 12c I got errror:

      Please help how to resolve this issue.

      Thanks,

      jc

      =============================================================

      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.

      ===============================================================

       

      SELECT    

                hdr.org_id,

                hdr.legal_entity_id,

                hdr.customer_trx_id,

                hdr.trx_date    AS  invoice_dt,

                hdr.cust_tin,

                hdr.rac_bill_to_customer_name    AS  cust_name,

                hdr.raa_bill_to_concat_address    AS  cust_addr,

                hdr.comments   AS   description,

                hdr.invoice_no,

                hdr.BIR_PERMIT_NO as BIR_PERMIT_NO,

                line.vatable_sales_amt * NVL( hdr.exchange_rate,1 )  AS  vatable_sales_amt,

                line.vat_exempt_sales_amt * NVL( hdr.exchange_rate,1 )  AS vat_exempt_sales_amt,

                line.zero_rated_sales_amt  * NVL( hdr.exchange_rate,1 )  AS  zero_rated_sales_amt,

                line.vat_amt  * NVL( hdr.exchange_rate,1 )  AS   vat_amt,

                line.sr_disc_amt * NVL( hdr.exchange_rate,1 )  AS sr_disc_amt,

                line.reg_disc_amt * NVL( hdr.exchange_rate,1 )  AS reg_disc_amt

      FROM  

         ( SELECT

          ct.org_id,

          ct.legal_entity_id,

          ct.customer_trx_id,

          CASE

                          WHEN ctt.type = 'INV'  THEN 'SB' 

                          WHEN ctt.type = 'CM'  THEN 'CM'

                          WHEN ctt.type = 'DM'  THEN 'DM'

                          ELSE ctt.type

          END || ct.trx_number AS  invoice_no,

          ct.trx_number,

          ct.trx_date,

          ct.bill_to_customer_id,

          rac_bill_party.party_name  AS  rac_bill_to_customer_name,

          ct.comments,

          ct.exchange_rate,

          DECODE( raa_bill.cust_acct_site_id,  NULL,  NULL,

                          arh_addr_pkg.arxtw_format_address

                             ( raa_bill_loc.address_style, 

                               raa_bill_loc.address1, raa_bill_loc.address2,

                               raa_bill_loc.address3, raa_bill_loc.address4,

                               raa_bill_loc.city,     raa_bill_loc.county,

                               raa_bill_loc.state,    raa_bill_loc.province,

                               raa_bill_loc.postal_code,

                               ft_bill.territory_short_name) )  AS raa_bill_to_concat_address,

          REGEXP_REPLACE( taxp.rep_registration_number, '( *[[:punct:]])', '' ) AS cust_tin,

          (select Description from FND_LOOKUP_VALUES_VL where 1=1 and LookUp_Type = 'XX_YGC_OU_BIR_RDO' and LookUp_Code = FND_Global.Org_Name)  as BIR_PERMIT_NO

           FROM   ra_customer_trx   ct

                    INNER JOIN

                       ra_cust_trx_types   ctt

                       ON    ctt.cust_trx_type_id = ct.cust_trx_type_id

                    INNER JOIN

                       ra_batch_sources    bs

                       ON   bs.batch_source_id = ct.batch_source_id

                       AND UPPER( bs.name ) IN ( 'SALES INVOICE', 'SERVICE BILLING', 'PARTS SALES INVOICE', 'VEHICLE SALES INVOICE','CREDIT MEMO','BILLING STATEMENT' )

                    INNER JOIN

                       hz_cust_accounts    rac_bill

                       ON   rac_bill.cust_account_id = ct.bill_to_customer_id

                    INNER JOIN

                       hz_parties    rac_bill_party

                       ON   rac_bill_party.party_id = rac_bill.party_id

                    INNER JOIN

                       hz_cust_site_uses    su_bill

                       ON   su_bill.site_use_id = ct.bill_to_site_use_id

                    INNER JOIN

                       hz_cust_acct_sites    raa_bill

                       ON   raa_bill.cust_acct_site_id = su_bill.cust_acct_site_id

                    INNER JOIN

                       hz_party_sites    raa_bill_ps

                       ON   raa_bill_ps.party_site_id = raa_bill.party_site_id

                    INNER JOIN

                       hz_locations    raa_bill_loc

                       ON   raa_bill_loc.location_id = raa_bill_ps.location_id

                    LEFT OUTER JOIN

                       fnd_territories_vl    ft_bill

                       ON   ft_bill.territory_code = raa_bill_loc.country

                    LEFT OUTER JOIN

                       zx_party_tax_profile  taxp

                       ON   taxp.party_id = rac_bill_party.party_id

                       AND taxp.party_type_code = 'THIRD_PARTY'

         )  hdr

            INNER JOIN

               ( SELECT customer_trx_id,

                        SUM( CASE

                             WHEN line_type IN ('LINE', 'CB', 'CHARGES')  AND

                                     INSTR( tax_classification_code, 'OUTPUT' )  > 0

                             THEN

                                  extended_amount

                             ELSE 0

                             END ) AS vatable_sales_amt,                

                        SUM( CASE

                             WHEN line_type IN ('LINE', 'CB', 'CHARGES')  AND

                                  tax_classification_code IN ( 'AR ZERO-RATED' )

                             THEN

                                  extended_amount

                             ELSE 0

                             END ) AS zero_rated_sales_amt,

                        SUM( CASE

                             WHEN line_type = 'TAX' THEN

                                  extended_amount

                             ELSE 0

                             END ) AS vat_amt,

                        SUM( CASE

                             WHEN line_type = 'LINE'  AND

                                  description LIKE '%Senior Citizen Discount%'

                             THEN

                                  extended_amount

                             ELSE 0

                             END ) AS sr_disc_amt,

                        SUM( CASE

                             WHEN line_type = 'LINE'  AND                           

                                  description LIKE '%Regular Discount%'                           

                             THEN

                                  extended_amount

                             ELSE 0

                             END ) AS reg_disc_amt,

                          SUM( CASE

                             WHEN line_type IN ('LINE', 'CB', 'CHARGES')  AND

                                  (tax_classification_code IS NULL AND description NOT LIKE '%Regular Discount%' AND

                                  description NOT LIKE '%Senior Citizen Discount%' ) OR

                                  tax_classification_code IN ( 'AR NON-VAT', 'AR EXEMPT' )

                             THEN

                                  extended_amount

                             ELSE 0

                             END ) AS vat_exempt_sales_amt

                 FROM   ra_customer_trx_lines

                 GROUP  BY customer_trx_id

               ) line

               ON   line.customer_trx_id = hdr.customer_trx_id

            INNER JOIN

               ( SELECT DISTINCT customer_trx_id, gl_posted_date

                 FROM   ra_cust_trx_line_gl_dist_v

                 WHERE  gl_posted_date IS NOT NULL

               ) glpost

               ON  glpost.customer_trx_id = hdr.customer_trx_id

      ORDER BY hdr.trx_date, hdr.invoice_no

      /

        • 1. Re: 11g vs 12c
          ddf_dba

          "11g" is a marketing term that references Oracle version 11.1.0.x; I suspect you're actually using one of the 11.2.0.x releases.  You need to post the version as numbers such as 11.2.0.1, 11.2.0.4 or whichever actual release you have installed.  Only then can someone have any hope of providing an actual, usable answer to your question.  Additionally fixes were made to 12.1.0.x that correct situations that should have generated an error in 11.2.0.x versions and this may be one of them.

           

          Until you post the exact version and platform (Windows, Linux, Unix, etc.) we have no way of knowing WHY this occurs.

           

           

          David Fitzjarrell

          • 2. Re: 11g vs 12c
            Beauty_and_dBest

            Hi bff (best friend forever),

             

             

            [orauat@r12 ~]$ sqlplus apps/apps

            SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 21 10:02:55 2016

            Copyright (c) 1982, 2013, Oracle.  All rights reserved.

            Connected to:

            Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

            With the Partitioning, OLAP, Data Mining and Real Application Testing options

             

             

            [oravisn@r12 ~]$ sqlplus apps/apps

            SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 21 10:00:53 2016

            Copyright (c) 1982, 2014, Oracle.  All rights reserved.

            Last Successful login time: Wed Dec 21 2016 09:56:45 +08:00

            Connected to:

            Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

            With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

             

             

            Kind regards,

            • 3. Re: 11g vs 12c
              Hemant K Chitale

              Don't use the ANSI join syntax.  Use the "legacy" join syntax.

               

              Hemant K Chitale

              • 4. Re: 11g vs 12c
                Hemant K Chitale

                MOS Document 9256994.8   states that the bug 9256994 has been fixed in 12.1 You might be hitting a re-manifestation of the same bug or a similar bug.

                 

                Hemant K Chitale

                • 5. Re: 11g vs 12c
                  Beauty_and_dBest

                  Thanks best friend Hemant,