Quarterly Partition non-deterministic error
Posted: Thu Apr 10, 2014 2:26 pm
Hello All,
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
Got result as was expecting
But now I try to implement the same while changing partition for a table this way
What is a non-deterministic function and any other way I can achieve quarterly partition on my data.
Thanks
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