Hi Tom

Am facing trouble with a simple query.
Basically, my ultimate goal here is to purge the data older than 2 years for few tables(which I have mentioned in purge_table_list table).
I have renamed all the partitions to the format of last 7 characters like 'JAN2014', 'FEB2015' etc.
In the below SQL, am trying to fetch the partition names for which I want to delete the data/ drop the partition (as of today data older than 14 FEB 2015). Weird thing here is that this query is working fine if I do not select partition_position column. Now, when I select it, it is giving 'Not a valid month' error. I have checked the data as well. It looks fine.
Can you help me with what could be the issue here.

select * from
(select ptl.table_name,
partition_name,
to_date(substr(partition_name,-7),'MONYYYY') as yearmonth,
add_months(sysdate,-24) as purgedate,
partition_position ---------------If I remove this column, query will work
from all_tab_partitions atp, purge_table_list ptl
where atp.table_name = ptl.table_name
)abc
where
yearmonth <= purgedate

Thanks