Skip to Content
0

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

Jan 07 at 06:36 AM

221

avatar image

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

10 |10000 characters needed characters left 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?

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Jens Gleichmann Jan 07 at 01:32 PM
0

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


Share
10 |10000 characters needed characters left characters exceeded
Debanshu Mukherjeee Jan 07 at 04:36 PM
0

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


Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Debanshu,

seems to be an issue how HANA handles the create table as syntax:

Just create the structure of the table and than try to fill it with data. May be this is going to work.

Which exact SPS12 revision are you currently using?

The field 'Number of columns in a column store table' is missing in the limitation view in the systems.

Regards,

Jens

0

Thanks.Yes will try that and see.

Meanwhile, we are also planning to upgrade to 122.10. We are on 122.06.

Regards
Debanshu

0

By the way, that SAP Note was updated after we reported the issue.
It was updated so that customers know the limitations.

Reg
Debanshu

0

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

0
Debanshu Mukherjeee Jan 08 at 06:58 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded