Temporal Tables - Possible Anamaly or Behaviour change in TD 15.1

Database
Enthusiast

Temporal Tables - Possible Anamaly or Behaviour change in TD 15.1

I see an anomaly with Temporal Tables, Same statement when encapsulated under a View behaves very different when queried directly on the Direct table,  I guess, when we were in TD 14.1 Release this behavior was not there. Can someone please explain me, if there was any difference between 14.1 and 15.1 on Temporal SEQUENCED VALID time SQL Executions?

 

SEL * FROM DV1_PCMH_SV.THL_MSTR_PAT_PGM_ELGBY_T

-- 588411 Rows retrieved

 

LOCKING ROW FOR ACCESS

SEQUENCED VALIDTIME

SEL * FROM DV1_PCMH_M.THL_MSTR_PAT_PGM_ELGBY

-- 614113  Rows Retrieved

 

View Definition 

replace view DV1_PCMH_SV.THL_MSTR_PAT_PGM_ELGBY_T as locking row for access
SEQUENCED VALIDTIME
select
"MSTR_PAT_ID"
,"PGM_MNEM_CD"
,"ELGBY_SPN"
,BEGIN (ELGBY_SPN) AS ELGBY_BG_DT
,LAST (ELGBY_SPN) AS ELGBY_END_DT
,"LVL_2_CMGT_STS_IND"
,"EXCL_IND"
,"DCS_EXCL_IND"
,"TENN_CRE_ELGBY_IND"
,"RTF_STY_STS_IND"
,"NF_STY_STS_IND"
,"MCO_RMVL_STS_IND"
,"DUAL_STS_IND"
,"CLMS_ELGBY_IND"
,"OPT_OUT_STS_IND"
,"PRIM_SRC_MBR_ID"
,"CDW_DQ_CD"
,"CDW_SRC_SYS_ID"
,"CDW_JOB_CTRL_ID"
,"CDW_INS_DT"
,"CDW_UPD_TS"
,"ELGBY_CAT_CD"
From DV1_PCMH_M.THL_MSTR_PAT_PGM_ELGBY ;

4 REPLIES
Enthusiast

Re: Temporal Tables - Possible Anamaly or Behaviour change in TD 15.1

Hi,

The difference is becasue when you run the sql standalone, the default is current valistime whereas in view it is sequenced valistime. Can you change the sequenced valistime to current valistime in view and check if they match ?

 

--samir

Enthusiast

Re: Temporal Tables - Possible Anamaly or Behaviour change in TD 15.1

If you see, both the places we have Sequenced Valid Time mentioned. In the View its embedd in the View Definition and when I am querying the table, I have the statement SEQUENCED VALID TIME, 

I am exactly not sure why it is behaving Different between when SEQUENCED VALID TIME is enacapsulated in a View Definition, compared to usage of it directly on the table.

Enthusiast

Re: Temporal Tables - Possible Anamaly or Behaviour change in TD 15.1

You mean you have sequenced validtime before the below sql ? But,i  dont see it here in your post. If you dont specify anything, it is by default current validtime.

SEL * FROM DV1_PCMH_SV.THL_MSTR_PAT_PGM_ELGBY_T

Enthusiast

Re: Temporal Tables - Possible Anamaly or Behaviour change in TD 15.1

Thats a View, I gave the View Definition where the SEQUENCED VALID TIME is present at the top of the SQL.

 

Based on what you said, Since I did'nt provide SEQUENCED VALID TIME before the SQL, it overrides to Current Valid Time, Even though SEQUENCED VALID Time is part of the View.

 

Thanks for your Time and reply, appreciate it.