Getting error while copying data from one temporal table to other temporal table

Database
Enthusiast

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

6 REPLIES
Enthusiast

Re: Getting error while copying data from one temporal table to other temporal table

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

Enthusiast

Re: Getting error while copying data from one temporal table to other temporal table

Hi

Try this.

CREATE MULTISET TABLE XXX.COPY AS XXX.MAIN WITH DATA AND STATS ;

Please let know the result

Thanking You

Santanu

Enthusiast

Re: Getting error while copying data from one temporal table to other temporal table

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

Enthusiast

Re: Getting error while copying data from one temporal table to other temporal table

Hi

Did you have any join index defined on the source table ?

Thanking You

Santanu

Enthusiast

Re: Getting error while copying data from one temporal table to other temporal table

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

Enthusiast

Re: Getting error while copying data from one temporal table to other temporal table

below sql works

nontemporal insert into XXX.Copy

 sel * from  XXX.electronicaddress