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
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
Got result as was expecting
201104
201203
But now I try to implement the same while changing partition for a table this way
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
What is a non-deterministic function and any other way I can achieve quarterly partition on my data.
Thanks
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.