Re: Partitioning question (duplicate?)
that's what I get for not testing but just reading the manual :)
remind me not to answer questions when I don't have a database
handy.
sounds like Dan's going to have to add a column.
--- Kirtikumar Deshpande <[EMAIL PROTECTED]> wrote:
> Rahcel, Dan:
>
> I played with such things a long time ago
>
> Here's the text for ORA-14120 error that I used to get:
>
> 14120, 0, "incompletely specified partition bound for a DATE
> column"
> // *Cause: An attempt was made to use a date expression whose format
>
> // does not fully (i.e. day, month, and year (including
> century))
> // specify a date as a partition bound for a DATE column.
> // The format may have been specified explicitly (using
> // TO_DATE() function) or implicitly (NLS_DATE_FORMAT).
> // *Action: Ensure that date format used in a partition bound for a
> // DATE column supports complete specification of a date
> // (i.e. day, month, and year (including century)).
> // If NLS_DATE_FORMAT does not support complete
> // (i.e. including the century) specification of the year,
> // use TO_DATE() (e.g. TO_DATE('01-01-1999', 'MM-DD-')
> // to fully express the desired date.
>
> And here is what I just tested to make sure it has (DATE in range
> partitions) not changed in
> 9.2.0.4 (AIX 4.3.3):
>
> kirti @dbmt : SQL> l
> 1 CREATE TABLE Orders
> 2(order_id NUMBER,
> 3 order_dt DATE,
> 4 cust_id NUMBER)
> 5 PARTITION BY RANGE(order_dt)
> 6 (PARTITION JanOrd VALUES LESS THAN
> 7 (TO_DATE('02','MM')),
> 8PARTITION FebOrd VALUES LESS THAN
> 9 (TO_DATE('03','MM')),
> 10PARTITION MarOrd VALUES LESS THAN
> 11* (TO_DATE('04','MM')))
> kirti @dbmp : SQL> /
> (TO_DATE('02','MM')),
> *
> ERROR at line 7:
> ORA-14120: incompletely specified partition bound for a DATE column
>
> kirti @dbmp : SQL>
>
> If anyone has any tricks to get around this issue, I would love to
> hear.
>
> Cheers!
>
> - Kirti
>
>
>
> --- Rachel Carmichael <[EMAIL PROTECTED]> wrote:
> > First time I've seen this post. And from the fine Data Warehousing
> > manual:
> >
> > here's an example of range partitioning. Note the "to_date" in the
> > values clause. I don't see why you couldn't use
> > to_date(date_column,'MONTH')
> >
> > Rachel
> >
> >
> > CREATE TABLE sales
> > (s_productid NUMBER,
> >s_saledate DATE,
> >s_custid NUMBER,
> >s_totalprice NUMBER)
> > PARTITION BY RANGE(s_saledate)
> > (PARTITION sal99q1 VALUES LESS THAN
> > (TO_DATE('01-APR-1999','DD-MON-')),
> > PARTITION sal99q2 VALUES LESS THAN
> > (TO_DATE('01-JUL-1999','DD-MON-')),
> > PARTITION sal99q3 VALUES LESS THAN
> > (TO_DATE('01-OCT-1999', 'DD-MON-')),
> > PARTITION sal99q4 VALUES LESS THAN
> > (TO_DATE('01-JAN-2000', 'DD-MON-')),
> > PARTITION sal00q1 VALUES LESS THAN
> > (TO_DATE('01-APR-2000', 'DD-MON-')),
> > PARTITION sal00q2 VALUES LESS THAN
> > (TO_DATE('01-JUL-2000', 'DD-MON-')),
> > PARTITION sal00q3 VALUES LESS THAN
> > (TO_DATE('01-OCT-2000', 'DD-MON-')),
> > PARTITION sal00q4 VALUES LESS THAN
> > (TO_DATE('01-JAN-2001', 'DD-MON-')));
> >
> >
> > --- Daniel Fink <[EMAIL PROTECTED]> wrote:
> > > Pardon if this is a duplicate, but the original has not shown up
> > > on the list after 3 hours...
> > >
> > > Is it possible in 9.2 to partition on a function?
> > >
> > > I have a table with a date column and I would like to partition
> > > by month, regardless of the year. For example, data from January
> > > 2003 or January 2004 would go into the same partition. Any
> > > sneaky ideas on how to accomplish this without changing the data
> > > structures.
> > >
> > > Daniel Fink
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Daniel Fink
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> > > San Diego, California-- Mailing list and web hosting
> services
> > >
> -
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
> in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You
> may
> > > also send the HELP command for other information (like
> subscribing).
> >
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
> > http://hotjobs.sweepstakes.yahoo.com/signingbonus
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web
Re: Partitioning question (duplicate?)
The only way I see is using a system-maintained ( through a before-insert and if necessary before-update trigger ) field that is set to to_char(,'mm') and then range partition on that. At 03:24 PM 1/14/2004, you wrote: Pardon if this is a duplicate, but the original has not shown up on the list after 3 hours... Is it possible in 9.2 to partition on a function? I have a table with a date column and I would like to partition by month, regardless of the year. For example, data from January 2003 or January 2004 would go into the same partition. Any sneaky ideas on how to accomplish this without changing the data structures. Daniel Fink -- Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning question (duplicate?)
Dan,
Good question, but unless I'm misinterpreting the results, the answer is
no...
SQL> show release
release 902000100
SQL> create table test
2 (a date, b number, c number)
3 partition by list (to_char(a, 'MON'))
4 (partition pJAN values ('JAN')),
5 (partition pFEB values ('FEB'))
6 (partition pMAR values ('MAR'))
7 (partition pAPR values ('APR'))
8 (partition pMAY values ('MAY'));
partition by list (to_char(a, 'MON'))
*
ERROR at line 3:
ORA-00907: missing right parenthesis
..seems to clearly be interpreting the phrase "to_char" as a column name...
Hope this helps...
-Tim
on 1/14/04 3:24 PM, Daniel Fink at [EMAIL PROTECTED] wrote:
> Pardon if this is a duplicate, but the original has not shown up
> on the list after 3 hours...
>
> Is it possible in 9.2 to partition on a function?
>
> I have a table with a date column and I would like to partition
> by month, regardless of the year. For example, data from January
> 2003 or January 2004 would go into the same partition. Any
> sneaky ideas on how to accomplish this without changing the data
> structures.
>
> Daniel Fink
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Partitioning question (duplicate?)
First time I've seen this post. And from the fine Data Warehousing
manual:
here's an example of range partitioning. Note the "to_date" in the
values clause. I don't see why you couldn't use
to_date(date_column,'MONTH')
Rachel
CREATE TABLE sales
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE(s_saledate)
(PARTITION sal99q1 VALUES LESS THAN
(TO_DATE('01-APR-1999','DD-MON-')),
PARTITION sal99q2 VALUES LESS THAN
(TO_DATE('01-JUL-1999','DD-MON-')),
PARTITION sal99q3 VALUES LESS THAN
(TO_DATE('01-OCT-1999', 'DD-MON-')),
PARTITION sal99q4 VALUES LESS THAN
(TO_DATE('01-JAN-2000', 'DD-MON-')),
PARTITION sal00q1 VALUES LESS THAN
(TO_DATE('01-APR-2000', 'DD-MON-')),
PARTITION sal00q2 VALUES LESS THAN
(TO_DATE('01-JUL-2000', 'DD-MON-')),
PARTITION sal00q3 VALUES LESS THAN
(TO_DATE('01-OCT-2000', 'DD-MON-')),
PARTITION sal00q4 VALUES LESS THAN
(TO_DATE('01-JAN-2001', 'DD-MON-')));
--- Daniel Fink <[EMAIL PROTECTED]> wrote:
> Pardon if this is a duplicate, but the original has not shown up
> on the list after 3 hours...
>
> Is it possible in 9.2 to partition on a function?
>
> I have a table with a date column and I would like to partition
> by month, regardless of the year. For example, data from January
> 2003 or January 2004 would go into the same partition. Any
> sneaky ideas on how to accomplish this without changing the data
> structures.
>
> Daniel Fink
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Fink
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Partitioning question (duplicate?)
Pardon if this is a duplicate, but the original has not shown up on the list after 3 hours... Is it possible in 9.2 to partition on a function? I have a table with a date column and I would like to partition by month, regardless of the year. For example, data from January 2003 or January 2004 would go into the same partition. Any sneaky ideas on how to accomplish this without changing the data structures. Daniel Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
