create or replace function check_is_active(fdate date, tdate date)
return int deterministic
as
vReturn int := 0;
begin
if sysdate between fdate and tdate then
vReturn := 1;
end if;
return vReturn;
end check_is_active;
/
create table TEST_TABLE (
ID int,
ACTIVE_FROM date not null,
ACTIVE_TO date,
IS_ACTIVE int as (check_is_active(ACTIVE_FROM, ACTIVE_TO)) virtual,
constraint TEST_TABLE_PK primary key (ID)
);
/
insert into TEST_TABLE (ID, ACTIVE_FROM, ACTIVE_TO) values (1, to_date('20000101', 'yyyymmdd'), to_date('21001231', 'yyyymmdd'));
insert into TEST_TABLE (ID, ACTIVE_FROM, ACTIVE_TO) values (2, to_date('20220824', 'yyyymmdd'), to_date('20220825', 'yyyymmdd'));
insert into TEST_TABLE (ID, ACTIVE_FROM, ACTIVE_TO) values (3, to_date('20230101', 'yyyymmdd'), to_date('21001231', 'yyyymmdd'));
insert into TEST_TABLE (ID, ACTIVE_FROM, ACTIVE_TO) values (4, to_date('20000101', 'yyyymmdd'), to_date('20220823', 'yyyymmdd'));
commit;
/