All Forums Database
05 Mar 2014
Getting error while copying data from one temporal table to other temporal table

CREATE MULTISET TABLE XXX.electronicaddress ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      AddressId VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      ElectronicAddressTxt VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      ValidityPeriod PERIOD(TIMESTAMP(6) WITH TIME ZONE),

      DataSourceCd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      DataProviderCd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      DataProviderChainTxt VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC,

      FirstRegisteredDttm TIMESTAMP(6) WITH TIME ZONE,

      LastChangedDttm TIMESTAMP(6) WITH TIME ZONE,

      LastChangedReasonCd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      LastChangedByLogonId VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,

      DILastBusinessDttm TIMESTAMP(6) WITH TIME ZONE,

      DILastChangedFromSRCInd VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS ('Y','N'),

      DIActiveRowInd VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS ('Y','N'),

      DILastActiveRowInd VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS ('Y','N'),

      EDWBusinessTimePeriod PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS VALIDTIME,

      EDWBusinessTimeIntraDayInd VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS ('Y','N'),

      EDWCreatedParcelId VARCHAR(60) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

      EDWClosedParcelId VARCHAR(60) CHARACTER SET LATIN CASESPECIFIC COMPRESS ,

      EDWTransTimePeriod PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME,

      EDWSoftDeleteInd VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS ('Y','N'),

      EDWTemporalInd VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'N' COMPRESS ('Y','N'), 

CONSTRAINT ElectronicAddressUV CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME UNIQUE ( AddressId ))

PRIMARY INDEX NUPIElectronicAddress ( AddressId )

 

the above is my DDL, I am trying to take a copy of the table, I should also ensure that the Temporal characteristics should be enabled in the new copy table also, could some body help me in this

05 Mar 2014

When I try to copy using the below query
nontemporal insert into XXX.Copy
nonsequenced validtime and nonsequenced transactionTime sel * from  XXX.electronicaddress
I am getting the error
INSERT Failed. 3706:  Syntax error: Temporal qualifier must not be specified for select statement. 
Even I removed EDWTransTimePeriod  and EDWBusinessTimePeriod  also but still getting the same error

Santanu84 122 posts Joined 04/13
06 Mar 2014

Hi
Try this.
CREATE MULTISET TABLE XXX.COPY AS XXX.MAIN WITH DATA AND STATS ;
Please let know the result
 
Thanking You
Santanu

07 Mar 2014

Hi,
I am getting the below error
CREATE TABLE Failed. 9381:  System-defined join index can not be created on the target table during CREATE TABLE AS WITH DATA operation. 
But fortunately, I created a manual copy of table and moved data into the tables using the below script
nontemporal insert into XXX.Copy
( col1,col2,col3,col_transtime_temporal,col_validtime_temporal)
sel col1,col2,col3,col_transtime_temporal,col_validtime_temporal 
from  XXX.electronicaddress
 

Santanu84 122 posts Joined 04/13
07 Mar 2014

Hi
 
Did you have any join index defined on the source table ?
Thanking You
Santanu

LUCAS 56 posts Joined 06/09
02 Jul 2014

Prathap,
 
i experimented that situation, sort of a confusing mix of INSERT (into a non temporal data in my test) and SELECT from a temporal data.
You could try with a derived table:
"insert into XXX.Copy
SELECT * from ( nonsequenced validtime and nonsequenced transactionTime sel * from  XXX.electronicaddress) Wtable
;"
Pierre

anil.doobagunta 11 posts Joined 10/11
22 Jun 2015

below sql works
nontemporal insert into XXX.Copy
 sel * from  XXX.electronicaddress

You must sign in to leave a comment.