I am trying to change table's partition expression from monthly to quarterly.
The column on which the partition is made is an integer column having value like
20120230
I tried this query to extract the quaerter for each year and see my partition_key
Code: Select all
select distinct(partition_key) from (select cast((EXTRACT(year FROM cast(cast(xxxx as varchar)as date))*100 + extract (quarter from cast(cast(xxxx as varchar)as date))) as varchar) as partition_key from xx.xxxx) sub
Code: Select all
201104
201203
But now I try to implement the same while changing partition for a table this way
Code: Select all
ALTER TABLE xx.xxxx PARTITION BY (EXTRACT(year FROM cast(cast(xxxx as varchar)as date))*100 + extract (quarter from cast(cast(xxxx as varchar)as date))) ;
I get this error
NOTICE 4954: The new partitioning scheme will produce 3 partitionsROLLBACK 2552: Cannot use meta function or non-deterministic function in PARTITION BY expression
Thanks