Skip to Content

HANA create column table error - number of columns exceeds its maximum

I am trying to create a column store table which gets populated by

SELECT * FROM a CalcView.

This CalcView has around 1253 columns and I thought that this shouldn't be a problem since the SQL GUide for SPS12 (that's our version) clearly mentions that the colum limit for a column store table is 64000 and for normal table 1000, so why am I receiving this error when I am trying to create a column table?

Here's my SQL which I am executing:
SQL:
CREATE COLUMN TABLE "STAGING"."STA_FIN_STAR_RT_EN"
AS (SELECT TOP 100 * FROM "_SYS_BIC"."002_REPORTING_FOUNDATION.Finance_Transaction_Summary/CV_RPFO_TX_FIN_STAR_RT" ('PLACEHOLDER' = ('$IP_FISCAL_PERIOD_FROM$', '001'), 'PLACEHOLDER' = ('$IP_FISCYEAR_FROM$', '2018'), 'PLACEHOLDER' = ('$IP_FISCYEAR_TO$', '2018'), 'PLACEHOLDER' = ('$IP_FISCAL_PERIOD_TO$', '001'), 'PLACEHOLDER' = ('$IP_LANGU$', 'E')));

Error:
Could not execute 'CREATE COLUMN TABLE "STAGING"."STA_FIN_STAR_RT_EN"
AS (SELECT TOP 100 * FROM ...' in 1:11.736 minutes . SAP DBTech JDBC: [442]: number of columns exceeds its maximum: maximum is 1000, but you specified 1253 columns

Add comment
10|10000 characters needed characters exceeded

  • The limitations can be different on each HANA system depending on the hardware or configuration. Can you check it by querying the corresponding limitation value from system view M_SYSTEM_LIMITS?

  • Get RSS Feed

3 Answers

  • Jan 07 at 01:32 PM

    Hi Debanshu,

    please check following statement:

    SELECT * FROM M_SYSTEM_LIMITS;

    2154870 - How-To: Understanding and defining SAP HANA Limitations

    I just checked it in my test environment with Rev. 122.13:

    SAP documentation for SPS12 says also only 1000 cols for a CS table. The same in HANA 2.0 with latest SPS => 2.

    If you create a view with more cols than 1000, this will be possible but not with a table.

    Regards,

    Jens

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 07 at 04:36 PM

    Hi Jens,

    Thanks for the prompt answer. However the 64000 limit stands valid for Column Store tables as well.

    Here's the link for HANA SPS12 SQL Guide I am referring
    https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/1.0.12/en-US/20a760537519101497e3cfe07b348f3c.html

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Jens,

      Finally, I was able to crate the column table with a simple Create Column Table command without 'as' clause, but the arduous part was copying the definition from the Column View and formatting it to fit into the create format.

      Regards
      Debanshu

  • Jan 08 at 06:58 AM

    Hi,

    SAP has confirmed that this is a bug in the system and is also a known issue.
    We are on 122.06 and as per SAP, this bug is resolved in 122.10 and also they have delivered a fix for this in HANA 2.0 in revisions 2.00.0002.02, 2.00.012.00, & 2.00.20.00.

    We have asked for any workaround for the time being and are awaiting a response from SAP.
    Meanwhile, if anyone has any suggestions to handle this in the interim, please let us know.

    Reg
    Debanshu

    Add comment
    10|10000 characters needed characters exceeded