CREATE OR REPLACE FUNCTION is_date(parameter DATE) RETURN NUMBER IS
BEGIN
IF parameter is null OR (parameter>=TO_DATE(’-4712-01-01’,‘SYYYY-MM-DD’) and parameter<=TO_DATE(‘9999-12-31’,‘SYYYY-MM-DD’)) then
RETURN 1;
else RETURN 0;
end if ;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
CREATE TABLE TT
AS
SELECT
CARD.BILLCODE,
CARD.ORGUNIT,
CARD.OBJECTID,
is_date(CARD.caiwrzrq) AS T1,
is_date(CARD.jizrq) AS T2,
is_date(CARD.qudrq) AS T3,
is_date(CARD.CHUZRQ) AS T4,
is_date(CARD.JUNGRQ) AS T5,
is_date(CARD.BAOXJZRQ) AS T6,
is_date(CARD.TOURSYRQ) AS T7,
is_date(CARD.ZHUCDJRQ) AS T8,
is_date(CARD.CHUBRQ) AS T9,
is_date(CARD.ENABLETIME) AS T10,
is_date(CARD.SHOUQGGZ) AS T11,
is_date(CARD.BAOXJZRQ) AS T12,
is_date(CARD.INVALIDTIME) AS T13,
is_date(CARD.CZ_GOUZRQ) AS T14,
is_date(CARD.BILLTIME) AS T15,
is_date(CARD.CREATETIME) AS T16,
is_date(CARD.AUDITTIME) AS T17,
is_date(CARD.MODIFYTIME) AS T18,
is_date(CARD.ZZSPRQ) AS T19,
is_date(CARD.SUBMITTIME) AS T20
FROM GAMS_ASSETCARD CARD
select * from TT
where t1=0
or t2=0
or t3=0
or t4=0
or t5 =0
or …t20 =0
