Re: range partition on char column

"mario" <xnazsco_at_tiscali.it> wrote in message news:pan.2006.07.07.08.49.12.364221_at_tiscali.it...
> Hi all,
> I'm using 9.2.0.1 on Solaris machine.
> I need to create a range partition table
> usign a CTAS syntax. The columns I have to
> use for range partition is a char(10) storing
> a date in the format yyyy-mm-dd (please don't
> ask me WHY!!! :) )
>
> I know that when we use a function to casting a
> datatype to another one, the order results can be
> different from the one aspected.
> But I've made some test, and the cast should work
> fine..but obviously it _does_ not work.
>
> following the scenario:
>
> original table:
>
> CCID NOT NULL NUMBER(10)
> CDATE NOT NULL CHAR(10)
> ............ other columns here ................
>
> new table (the one I should create partitioned)
>
> CREATE TABLE NEW
> TABLESPACE TABLE_AREA
> PARALLEL
> NOLOGGING
> PARTITION BY RANGE (cdate)
> (partition q1_2003 values less than
(to_Date('2003-05-01','yyyy-mm-dd')),
> partition q2_2003 values less than (to_Date('2003-09-01','yyyy-mm-dd')),
> partition q3_2003 values less than (to_Date('2004-01-01','yyyy-mm-dd')),
> partition q1_2004 values less than (to_Date('2004-05-01','yyyy-mm-dd')),
> partition q2_2004 values less than (to_Date('2004-09-01','yyyy-mm-dd')),
> partition q3_2004 values less than (to_Date('2005-01-01','yyyy-mm-dd')),
> ....... other partitions here ......................................
> partition extra values less than (maxvalue))
> AS SELECT * FROM OLD WHERE 1=2;
>
>
> When I try to create the table I receive the error:
>
> partition q2_2003 values less than (to_Date('2003-09-01','yyyy-mm-dd')),
> *
> ERROR at line 7:
> ORA-14037: partition bound of partition "Q2_2003" is too high
>
>
> To be confident with the order used by Oracle, I create a very simple
> table and tested the order by clause:
>
> SQL> create table test (testo char(10));
>
> SQL> insert into test values ( '2003-01-01');
> 1 row created.
>
> ... other inserts here ...........
>
>
> then I tested the order by clause:
>
>
> SQL> select * from test order by testo asc;
>
> TESTO
> ----------
> 2003-01-01
> 2003-05-01
> 2003-09-01
> 2004-01-01
> 2004-05-01
> 2004-09-01
> 2005-01-01
> 2005-05-01
> 2005-09-01
>
> 9 rows selected.
>
> SQL> select * from test order by testo desc;
>
> TESTO
> ----------
> 2005-09-01
> 2005-05-01
> 2005-01-01
> 2004-09-01
> 2004-05-01
> 2004-01-01
> 2003-09-01
> 2003-05-01
> 2003-01-01
>
> 9 rows selected.
>
> I _think_ that when I create the partition using char() column,
> also if the records will be inserted using the format
to_date('yyyy-mm-dd'),
> the order should be the right one. I mean, the alphabetical order should
> correspond to the chronological one.
>
> Where I'm wrong??
>
> any help will be appreciated
> regards
>
> M.
>
>

Mario,

looks like your ranges have overlap. Use something like BETWEEN.

Robbert Received on Fri Jul 07 2006 - 06:05:25 CDT