CREATE MULTISET TABLE TEMPTBL_1(COL_ID BIGINT NOT NULL,VLD_PDT PERIOD(DATE) NOT NULL AS VALIDTIME)PRIMARY INDEX ( COL_ID );
The case is: first I reassign "validtime date of" on a session level with statement
SET SESSION AS OF DATE '2016-06-06' -- Some date in the past;
Now If I'm querying
SELECT * FROM TEMPTBL_1;
CREATE MULTISET VOLATILE TABLE TEMPTBL_VolAS (SELECT * FROM TEMPTBL_1) WITH DATAPRIMARY INDEX ( COL_ID ) ON COMMIT PRESERVE ROWS ;
Everything's going well!
SELECT * FROM TEMPTBL_Vol;
It returns all rows that were valid on date 2016-06-06.
But if I'm trying to get result with INSERT-SELECT statment
CREATE MULTISET TABLE TEMPTBL_2(COL_ID BIGINT NOT NULL)PRIMARY INDEX ( COL_ID );INSERT INTO TEMPTBL_2 ( COL_ID )SELECT COL_ID FROM TEMPTBL_1;SELECT * FROM TEMPTBL_2;
The result set shows rows that are valid on current date! It seems that INSERT-SELECT ignores reassignment of "valid date" value on a session level.
INSERT INTO TEMPTBL_2 ( COL_ID )SELECT COL_ID FROM TEMPTBL_1 AS OF DATE '2016-06-06';
Everything will be ok - data would meet expectations.
rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.