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 <kirtikumar_deshpande_at_yahoo.com> 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, 00000, "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-YYYY')
> // 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')),
> 8 PARTITION FebOrd VALUES LESS THAN
> 9 (TO_DATE('03','MM')),
> 10 PARTITION 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 <wisernet100_at_yahoo.com> 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-YYYY')),
> > PARTITION sal99q2 VALUES LESS THAN
> > (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
> > PARTITION sal99q3 VALUES LESS THAN
> > (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
> > PARTITION sal99q4 VALUES LESS THAN
> > (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')),
> > PARTITION sal00q1 VALUES LESS THAN
> > (TO_DATE('01-APR-2000', 'DD-MON-YYYY')),
> > PARTITION sal00q2 VALUES LESS THAN
> > (TO_DATE('01-JUL-2000', 'DD-MON-YYYY')),
> > PARTITION sal00q3 VALUES LESS THAN
> > (TO_DATE('01-OCT-2000', 'DD-MON-YYYY')),
> > PARTITION sal00q4 VALUES LESS THAN
> > (TO_DATE('01-JAN-2001', 'DD-MON-YYYY')));
> >
> >
> > --- Daniel Fink <Daniel.Fink_at_Sun.COM> 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: Daniel.Fink_at_Sun.COM
> > >
> > > 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
> >
> > 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: ListGuru_at_fatcity.com (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
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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Jan 15 2004 - 04:44:24 CST