Home » SQL & PL/SQL » SQL & PL/SQL » PARTITION BY RANGE ON A TIMESTAMP DATATYPE COLUMN.. HELP!!
PARTITION BY RANGE ON A TIMESTAMP DATATYPE COLUMN.. HELP!! [message #18419] Wed, 30 January 2002 10:40 Go to next message
CATHYBEE
Messages: 20
Registered: January 2002
Junior Member
CREATE TABLE tbl_ClaimsHistory (
TotalAmount INTEGER NOT NULL,
StatementThrough TIMESTAMP(4) NOT NULL,
ProviderTaxID VARCHAR2(10) NULL,
FormType CHAR(1) NOT NULL,
ClientTransmitDate TIMESTAMP(4) NOT NULL,
ClaimType CHAR(1) NOT NULL,
ClientBridgeDate TIMESTAMP(4) NOT NULL,
ProductCode CHAR(1) NOT NULL,
RedirectionFlag CHAR(1) NULL,
DepositTime TIMESTAMP(4) NOT NULL,
TransmitTime TIMESTAMP(4) NULL,
ClaimID RAW(32) NOT NULL,
ServiceStateCode CHAR(1) NOT NULL,
BillCode CHAR(1) NOT NULL,
TransmitType CHAR(1) NOT NULL,
BillingDate TIMESTAMP(4) NULL,
EditMasterProductVersion VARCHAR2(8) NULL,
CoreProductVersion VARCHAR2(7) NULL,
EditMasterVersion VARCHAR2(8) NULL,
DRGCode VARCHAR2(3) NULL,
BillType VARCHAR2(3) NULL,
ServiceLineCount SMALLINT NULL,
PayerIndicator CHAR(1) NULL,
ThirdPartyID VARCHAR2(5) NULL,
CISID VARCHAR2(4) NOT NULL,
CarrierKey VARCHAR2(9) NOT NULL,
Mailbox VARCHAR2(5) NULL)
PARTITION BY RANGE(DepositTime)
(PARTITION D0200201
VALUES LESS THAN(TO_TIMESTAMP('01-FEB-2002 00:00:00.0000','DD-MON-YYYY HH:MI:SS.FFFF')),
PARTITION D0200202
VALUES LESS THAN(TO_TIMESTAMP('01-MAR-2002 00:00:00.0000','DD-MON-YYYY HH:MI:SS.FFFF')),
PARTITION D0200203 VALUES LESS THAN(MAXVALUE))
SQL> /
VALUES LESS THAN(TO_TIMESTAMP('01-FEB-2002 00:00:00.0000','DD-MON-YYYY HH:MI:SS.FFFF')),
*
ERROR at line 31:
ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals

Report message to a moderator

Re: PARTITION BY RANGE ON A TIMESTAMP DATATYPE COLUMN.. HELP!! [message #18421 is a reply to message #18419] Wed, 30 January 2002 11:21 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
what happens if you are using TO_TIMESTAMP_TZ
instead of TO_TIMESTAMP?

Report message to a moderator

Re: PARTITION BY RANGE ON A TIMESTAMP DATATYPE COLUMN.. HELP!!(MIKE:STILL GETTING THE SAME ERROR..) [message #18422 is a reply to message #18421] Wed, 30 January 2002 11:25 Go to previous messageGo to next message
CATHYBEE
Messages: 20
Registered: January 2002
Junior Member
SQL> /
VALUES LESS THAN(TO_TIMESTAMP_TZ('01-FEB-2002 00:00:00.0000','DD-MON-YYYY HH:MI:SS.FFFF')),
*
ERROR at line 31:
ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals

Report message to a moderator

Re: PARTITION BY RANGE ON A TIMESTAMP DATATYPE COLUMN.. HELP!! [message #139371 is a reply to message #18419] Tue, 27 September 2005 13:29 Go to previous message
whatisoracle
Messages: 1
Registered: September 2005
Junior Member
around 3 years since the question but for the reference of those who hit the page on a search, an extract of the syntax would be like:

partition TABPART200509 values less than (
TIMESTAMP'2005-10-01 00:00:00 +0:00') tablespace <tablespace name> ,
partition TABPART200510 values less than
(TIMESTAMP'2005-11-01 00:00:00 +0:00') tablespace <tablespace name>

Report message to a moderator

Previous Topic: START WITH [condition] CONNECT BY [condition]
Next Topic: creating a trigger
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed May 08 10:05:17 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.