通过在项目中使用雪花,我需要在每次激活某些过程时获得数据。数据必须来自特定的时间,并有助于改变机制。当我在select语句中设置时间戳时,比如
SELECT * FROM SOME_TABLE changes(INFORMATION => APPEND_ONLY) AT (TIMESTAMP => to_timestamp(':lastTime'));
我得到所有的信息,但是当我使用IIF函数(由于业务逻辑)做类似的事情时
SELECT * FROM SOME_TABLE changes(INFORMATION => APPEND_ONLY) AT (TIMESTAMP => IFF(1 = 0, CURRENT_TIMESTAMP, to_timestamp(':lastTime')));
SQL error [708] [02000]: Future data not yet for table SOME_TABLE.任何想法?
下面是一些修改的例子:
create or replace table t1 (
id number(8) not null,
c1 varchar(255) default null
);
alter table t1 set change_tracking = true;
set ts1 = (select current_timestamp());
insert into t1 (id,c1)
values
(1,'red'),
(2,'blue'),
(3,'green');
delete from t1 where id = 1;
update t1 set c1 = 'purple' where id = 2;
select *
from t1
changes(information => append_only)
at(timestamp => $ts1);
一切都如我所料。
然后我测试了你所说的IFF工作,但是你知道,只是证明每一步:
select *
from t1
changes(information => append_only)
at(timestamp => IFF(1 = 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP));
select *
from t1
changes(information => append_only)
at(timestamp => IFF(1 = 0, $ts1, $ts1));
select *
from t1
changes(information => append_only)
at(timestamp => IFF(1 = 0, CURRENT_TIMESTAMP, $ts1));
那都是快乐的。所以我特意做了一个"未来时间戳">
set ts2 = (select dateadd(hour, 1, current_timestamp()));
和使用相同的错误:
select *
from t1
changes(information => append_only)
at(timestamp => $ts2);
表T1的未来数据尚不可用。
使用if形式,它仍然会爆炸:
select *
from t1
changes(information => append_only)
at(timestamp => IFF(1 = 0, $ts2, $ts2));
但是如果我把未来放在未使用的那一半逻辑上:
select *
from t1
changes(information => append_only)
at(timestamp => IFF(1 = 0, $ts2, $ts1));
如我所愿。
这些都让我相信,你的时间戳是"在未来"。
至于为什么我不确定,但我很想在它上面放一个最小值来保护查询。
但:
at(timestamp => LEAST(CURRENT_TIMESTAMP,$ts2));
给出一个常量要求错误:
参数TIMESTAMP函数AT需要是常量,find 'LEAST('2022-06-20 22:58:21.566000000Z', $TS2)'
可能是嵌套的IFF
at(timestamp => IFF(CURRENT_TIMESTAMP<$ts2, CURRENT_TIMESTAMP, $ts2));
wozers, that fail also:
参数TIMESTAMP函数AT需要是常量,find 'IFF('2022-06-20 22:59:50.568000000Z' <TS2美元,2022-06-20>
这意味着你需要限制它是一个先验语句。
set safe_ts = (select LEAST(CURRENT_TIMESTAMP, $ts2));
select *
from t1
changes(information => append_only)
at(timestamp => $safe_ts);