Sysdate in virtual oracle column
I am trying to create a virtual column in oracle that uses a case statement, but if I call SYSDATE function it gave me this error:
ORA-54002: only pure functions can be specified in a virtual column expression
This is the request:
alter table t_requirements ADD
REQUIREMENT_STATE varchar2(30)
generated always as(
CASE
WHEN t_requirements.activation_date- SYSDATE - 5 <= 0 AND
t_requirements.activation_date - SYSDATE > 0 THEN 'Exist'
WHEN t_requisiti.activation_date - SYSDATE <=0 THEN 'Active'
END) virtual;
source to share
You are using SYSDATE
to create a virtual column. This is unacceptable because it is SYSDATE
not deterministic, i.e. Doesn't always return the same value. Imagine you are creating an index on this column. In a second, the index will already be invalid!
It seems like you should write a view that contains this dedicated computed column.
source to share
sysdate
is not deterministic. Its value can change every time we run it. Virtual columns must be deterministic. Otherwise, a strange situation would arise when a request to a record would change its value. Quite rightly, Oracle doesn't allow this.
This is a scenario in which we still have to use a query (perhaps as a table view) to display a derived value.
select r.*
, cast(
CASE
WHEN r.activation_date- SYSDATE - 5 <= 0 AND
r.activation_date - SYSDATE > 0 THEN 'Exist'
WHEN r.activation_date - SYSDATE <=0 THEN 'Active'
else 'Inactive'
END
as varchar2(30)) as REQUIREMENT_STATE
from requirements r
;
By the way, does your CASE statement need ELSE? To display anything other than whitespace when activation_date
greater than SYSDATE + 5
source to share
You can use SYSDATE
to support virtual column. The virtual column is calculated at query time. If you create a function ISACTIVE_FROM_TO
like:
CREATE OR REPLACE FUNCTION ISACTIVE_FROM_TO (p_active_from IN DATE, p_active_to IN DATE)
RETURN number
DETERMINISTIC
IS
BEGIN
RETURN (CASE WHEN SYSDATE BETWEEN NVL (p_active_from, SYSDATE) AND NVL (p_active_to, SYSDATE) THEN 1 ELSE 0 END);
EXCEPTION
WHEN OTHERS THEN
RAISE VALUE_ERROR;
END;
/
And define your virtual column in your table with ACTIVE_FROM and ACTIVE_TO date columns:
alter table xyz
add ISACTIVE NUMBER(1)
GENERATED ALWAYS AS (ISACTIVE_FROM_TO"(ACTIVE_FROM,ACTIVE_TO));
The table will dynamically calculate the value based on SYSDATE
. The function is deterministic ... given the same from / to dates and sysdate, it will always return the same value. The function must have the deterministic keyword.
As sysdate approaches the date / time ACTIVE_TO, successive queries on the table will cause the ISACTIVE value to go from 1 to 0 .... as the function calculates the value based on the SYSDATE move. Works very much the same as a separate view.
source to share