Skip to Content
-1
May 12, 2020 at 09:56 AM

Most basic type of Range Partition Pruning for a DATE Column not possible ?

672 Views

Hello,

partitioning on a DATE Column for partiztion Pruning not possible ?

But it should:

https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.04/en-US/8dd866a688ec4914a074727a2c800142.html

2416490 - FAQ: SAP HANA Data Aging in SAP S/4HANA

Got a Table:

CREATE TABLE MYPART ( XINTEGER INTEGER

, YDATE DATE

, ZVARCHAR2 VARCHAR(2)

);

...add a Primary Key:

ALTER TABLE MYPART add constraint MYPART Primary KEY( XINTEGER

, YDATE );

...add a partition on the YDATE , the DATE Column

ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY')

...and get:

Could not execute 'ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY')' in 32 ms 321 µs . SAP DBTech JDBC: [2048]: column store error: fail to alter partition: [2594] General partitioning error;Error when handling partition specification. Value '01.01.2000' could not be converted into an integer.


Other try with OTHERS

ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY' , PARTITION OTHERS)

...and get the same:

Could not execute 'ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION '01.01.2000' <= VALUES < 'DD.MM.YYYY' , ...' in 35 ms 629 µs . SAP DBTech JDBC: [2048]: column store error: fail to alter partition: [2594] General partitioning error;Error when handling partition specification. Value '01.01.2000' could not be converted into an integer.

Might think formating wiill help:

ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION to_DATE('01.01.2000', 'DD.MM.YYYY') <= VALUES < to_DATE('DD.MM.YYYY', 'DD.MM.YYYY') , PARTITION OTHERS)

...and get:

Could not execute 'ALTER TABLE MYPART PARTITION BY RANGE (YDATE) ( PARTITION to_DATE('01.01.2000', 'DD.MM.YYYY') <= ...' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "to_DATE": line 1 col 59 (at pos 59)

No Range Partitioning on a DATE Column ???

Best Martin