Netezza INSERT - 忽略无效日期?
问题描述:
有没有可以告诉Netezza忽略无效日期的选项或设置?在下面的例子中整个INSERT失败,因为源表的第三行有一个无效的日期。我期望违规行可能会被跳过,但插入零行似乎是一个极端的事情。Netezza INSERT - 忽略无效日期?
我尝试了以下,但它也失败了。另一种方法是解析源日期和检验的有效性每个组件(包括天/月,闰年等)
insert into db.test (cmclmn, effdt, efftm)
select cmclmn, case when to_date(effdt,'yyyymmdd') is null
then null
else to_date(effdt,'yyyymmdd') end,
cast(lpad(efftm,6,0) as time) as efftm
from db.test_src
;
整个脚本:
CREATE TABLE db.test
(
cmclmn integer,
effdt date,
efftm time
)
DISTRIBUTE ON (cmclmn);
drop table db.test_src;
create table db.test_src
(
cmclmn integer,
effdt integer,
efftm integer)
distribute on (cmclmn);
insert into db.test_src (cmclmn, effdt, efftm) values (1,20140120, 102000);
insert into db.test_src (cmclmn, effdt, efftm) values (2,20140121, 231212);
insert into db.test_src (cmclmn, effdt, efftm) values (3,0,111111);
insert into db.test_src (cmclmn, effdt, efftm) values (4,2014,90909);
insert into db.test (cmclmn, effdt, efftm)
select cmclmn, to_date(effdt,'yyyymmdd'), cast(lpad(efftm,6,0) as time) as efftm
from db.test_src
;
答
您可以创建一个用户定义的函数做一个完整的测试。对于你来说,也许有些事情简单做会:
insert into db.test (cmclmn, effdt, efftm)
select cmclmn, to_date(effdt,'yyyymmdd'), cast(lpad(efftm,6,0) as time) as efftm
from db.test_src
where effdt between 20010000 and 20150000 and
effdt % 10000 between 0101 and 1231 and
effdt between 01 and 31;
我不知道,如果Netezza公司不断优化查询,所以to_date()
可能过滤器之前运行。如果是这样,那么使用case
还有:
insert into db.test (cmclmn, effdt, efftm)
select cmclmn,
(case when effdt between 20010000 and 20150000 and
effdt % 10000 between 0101 and 1231 and
effdt % 100 between 01 and 31
then to_date(effdt,'yyyymmdd')
end),
cast(lpad(efftm,6,0) as time) as efftm
from db.test_src
where effdt between 20010000 and 20150000 and
effdt % 10000 between 0101 and 1231 and
effdt % 100 between 01 and 31;
日期检查是不完美的,但它可能足以满足您的目的。
答
Netezza Analytics Package 3.0附带一些示例LUA函数,isdate()和todate()是其中的两个函数。
这些函数在查询返回代码之前捕获返回代码。 isdate()返回布尔结果,告诉您日期是否有效,todate()将返回Netezza日期值,如果传入参数不是有效日期,则返回null。
谢谢。