Re: Date column as a partition key question

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 13 Nov 2002 18:16:59 -0800
Message-ID: <130ba93a.0211131816.500df7ff@posting.google.com>

A couple of problems:
1. I don't think you currently can use a function after the "PARTITION BY RANGE" clause. For example:

SQL> create table tyu (c1 number, c2 varchar2(2))

  2  partition by range (to_number(c2))
  3  (partition p1 values less than (5),
  4  partition p2 values less than (10),

  5 partition p3 values less than (maxvalue)); partition by range (to_number(c2))

                             *

ERROR at line 2:
ORA-00907: missing right parenthesis

2. What about the year? Is it your intention to partition by month alone? This may not be possible the way the table is set up. Unless you have a month column and you partition by that column.

3. If you want to partition by "date", not by month alone. You do like   1 create table tyu (c1 number, c2 date)   2 partition by range (c2)
  3 (partition p1 values less than
(to_date('01-jan-2002','dd-mon-yyyy')),
  4 partition p2 values less than
(to_date('01-feb-2002','dd-mon-yyyy')),
  5* partition p3 values less than (maxvalue)), SQL> / Table created.

• Jusung Yang

Ashche_at_yahoo.com (Alex) wrote in message news:<44f08c9d.0211130927.10b33598_at_posting.google.com>...
> Dear *,
> I have one question, why follozing script does not want to work:
> CREATE TABLE sales (invoice_no NUMBER,
> sale_date DATE NOT NULL,
> sale_id INT NOT NULL)
> PARTITION BY RANGE (
> (to_number (to_char (to_date (sale_date,
> 'YYYYMMDDHH24MISS' ), 'MM' )))
> )
> (partition jan VALUES LESS THAN (2),
> partition feb VALUES LESS THAN (3),
> partition mar VALUES LESS THAN (4),
> partition apr VALUES LESS THAN (5),
> partition may VALUES LESS THAN (6),
> partition jun VALUES LESS THAN (7)
> partition jul VALUES LESS THAN (8),
> partition sep VALUES LESS THAN (9),
> partition oct VALUES LESS THAN (10),
> partition nov VALUES LESS THAN (11)
> );
>
>
> Thanks
> Alex
Received on Wed Nov 13 2002 - 20:16:59 CST