Home » SQL & PL/SQL » SQL & PL/SQL » error ORA-01733- virtual column not allowed here (2 Merged) (10 g)
| error ORA-01733- virtual column not allowed here (2 Merged) [message #532601] |
Wed, 23 November 2011 10:04  |
 |
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
Hi all,
Does know why I am getting ORA-01733- virtual column not allowed here
Following :
CREATE TABLE EMPL
(
EMP_ID NUMBER(10),
CNT NUMBER(10)
)
insert into empl(EMP_ID)
values(1)
insert into empl(EMP_ID)
values(2)
CREATE OR REPLACE FORCE VIEW v_emp (emp_id, cnt)
AS
(SELECT emp_id,0
FROM empl)
select * from empl
EMP_ID
------
2
1
select * from v_emp
EMP_ID CNT
------ ---
2 0
1 0
update v_emp
set cnt = 2
where emp_id = 1
ORA-01733- virtual column not allowed here
Thanks in advance
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here [message #532608 is a reply to message #532601] |
Wed, 23 November 2011 10:23  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
View is a "logical table" or, if you will, a stored query. There is no physical table for a view. Column emp_id in your view maps to table empl column emp_id, so you could update it via view, but column cnt in your view does not map into any table column, so what should be updated? There is a chance you meant:
SQL> CREATE OR REPLACE VIEW v_emp (emp_id, cnt)
2 AS
3 (SELECT emp_id,cnt
4 FROM empl)
5 /
View created.
SQL> update v_emp
2 set cnt = 2
3 where emp_id = 1
4 /
1 row updated.
SQL> select * from v_emp
2 /
EMP_ID CNT
---------- ----------
1 2
2
SQL> select * from empl
2 /
EMP_ID CNT
---------- ----------
1 2
2
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here [message #532650 is a reply to message #532610] |
Wed, 23 November 2011 16:56 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
Thanks Barbara for reply ,
To explain more
CREATE OR REPLACE FORCE VIEW v_emp (emp_id, cnt)
AS
(SELECT emp_id,0
FROM emlp)
insert into empl(EMP_ID,VAC_BALANCE_DAY)
values(1,10)
insert into empl(EMP_ID,VAC_BALANCE_DAY)
values(2,35)
commit
SQL> select * from empl
2 /
EMP_ID VAC_BALANCE_DAY
---------- ---------------
1 10
2 35
CREATE OR REPLACE FUNCTION get_vac_net( p_value_parmerter number, p_vac_balance_day number)
RETURN number IS
v_vac_net number(10);
-- ----------------------------------------
BEGIN
v_vac_net := 360 - (p_value_parmerter * p_vac_balance_day );
RETURN v_vac_net;
-- ----------------------------------------
END;
/
CREATE OR REPLACE VIEW v_emp (emp_id, vac_balance_day,value_parmerter,net_vac)
AS
(SELECT emp_id,vac_balance_day,value_parmerter,get_vac_net(value_parmerter,vac_balance_day)
FROM empl)
I want pass any value to value_parmerter , and calculate this value by function and the result pass to net_vac,
How to pass value for value_parmerter in v_emp for every emp_id
pass value_parmerter 1 to emp_id = 1
and pass value_parmerter 3 to emp_id = 2
like this,,,
select * from v_emp
exception output
------------
EMP_ID VAC_BALANCE_DAY value_parmerter net_vac
---------- --------------- ------------- -------
1 10 1 350
2 35 3 255
[Updated on: Wed, 23 November 2011 17:27] Report message to a moderator |
|
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here [message #532654 is a reply to message #532653] |
Wed, 23 November 2011 17:52 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- table, data, and function:
SCOTT@orcl_11gR2> CREATE TABLE empl
2 (emp_id NUMBER,
3 vac_balance_day NUMBER)
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO empl (emp_id, vac_balance_day) VALUES (1, 10)
3 INTO empl (emp_id, vac_balance_day) VALUES (2, 35)
4 SELECT * FROM DUAL
5 /
2 rows created.
SCOTT@orcl_11gR2> SELECT * FROM empl
2 /
EMP_ID VAC_BALANCE_DAY
---------- ---------------
1 10
2 35
2 rows selected.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION get_vac_net
2 (p_value_parameter NUMBER,
3 p_vac_balance_day NUMBER)
4 RETURN NUMBER
5 IS
6 v_vac_net NUMBER(10);
7 BEGIN
8 v_vac_net := 360 - (p_value_parameter * p_vac_balance_day );
9 RETURN v_vac_net;
10 END get_vac_net;
11 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- additional table, view, and trigger:
SCOTT@orcl_11gR2> CREATE TABLE emp_id_and_value_param
2 (emp_id NUMBER,
3 value_parameter NUMBER)
4 /
Table created.
SCOTT@orcl_11gR2> CREATE OR REPLACE VIEW v_emp
2 (emp_id, vac_balance_day, value_parameter, net_vac)
3 AS
4 SELECT e1.emp_id,
5 e1.vac_balance_day,
6 e2.value_parameter,
7 get_vac_net (e2.value_parameter, e1.vac_balance_day)
8 FROM empl e1,
9 emp_id_and_value_param e2
10 WHERE e1.emp_id = e2.emp_id (+)
11 /
View created.
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER v_emp_iou
2 INSTEAD OF UPDATE ON v_emp
3 FOR EACH ROW
4 BEGIN
5 UPDATE emp_id_and_value_param
6 SET value_parameter = :NEW.value_parameter
7 WHERE emp_id = :NEW.emp_id;
8 IF SQL%ROWCOUNT = 0 THEN
9 INSERT INTO emp_id_and_value_param
10 VALUES (:NEW.emp_id, :NEW.value_parameter);
11 END IF;
12 END v_emp_iou;
13 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- updates and results:
SCOTT@orcl_11gR2> UPDATE v_emp
2 SET value_parameter = 1
3 WHERE emp_id = 1
4 /
1 row updated.
SCOTT@orcl_11gR2> UPDATE v_emp
2 SET value_parameter = 3
3 WHERE emp_id = 2
4 /
1 row updated.
SCOTT@orcl_11gR2> SELECT * FROM v_emp
2 /
EMP_ID VAC_BALANCE_DAY VALUE_PARAMETER NET_VAC
---------- --------------- --------------- ----------
1 10 1 350
2 35 3 255
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here [message #532806 is a reply to message #532805] |
Thu, 24 November 2011 17:05 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Before you edited your latest post, you asked about another option using collections instead of a view. Please see the additional option below.
-- table, data, and function:
SCOTT@orcl_11gR2> CREATE TABLE empl
2 (emp_id NUMBER,
3 vac_balance_day NUMBER)
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO empl (emp_id, vac_balance_day) VALUES (1, 10)
3 INTO empl (emp_id, vac_balance_day) VALUES (2, 35)
4 SELECT * FROM DUAL
5 /
2 rows created.
SCOTT@orcl_11gR2> SELECT * FROM empl
2 /
EMP_ID VAC_BALANCE_DAY
---------- ---------------
1 10
2 35
2 rows selected.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION get_vac_net
2 (p_value_parameter NUMBER,
3 p_vac_balance_day NUMBER)
4 RETURN NUMBER
5 IS
6 v_vac_net NUMBER(10);
7 BEGIN
8 v_vac_net := 360 - (p_value_parameter * p_vac_balance_day );
9 RETURN v_vac_net;
10 END get_vac_net;
11 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- objects and select using objects and function:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE params_typ AS OBJECT
2 (emp_id NUMBER,
3 value_parameter NUMBER)
4 /
Type created.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE params_tab AS TABLE OF params_typ;
2 /
Type created.
SCOTT@orcl_11gR2> SELECT e.emp_id, e.vac_balance_day, t.value_parameter,
2 get_vac_net (t.value_parameter, e.vac_balance_day) net_vac
3 FROM empl e,
4 TABLE
5 (params_tab
6 (params_typ (1, 1),
7 params_typ (2, 3))) t
8 WHERE e.emp_id = t.emp_id
9 /
EMP_ID VAC_BALANCE_DAY VALUE_PARAMETER NET_VAC
---------- --------------- --------------- ----------
1 10 1 350
2 35 3 255
2 rows selected.
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here [message #532813 is a reply to message #532806] |
Thu, 24 November 2011 22:11 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
Thanks a lot Barbara
--I create object and function
CREATE OR REPLACE TYPE OBJ_emp1 IS OBJECT
(
emp_id NUMBER(5),
vac_balance_day NUMBER(5),
value_parameter NUMBER(5),
net_vac NUMBER(5)
)
/
CREATE OR REPLACE TYPE t_emp1 AS TABLE OF OBJ_emp1;
CREATE OR REPLACE FUNCTION get_emp_vac(p_emp_id NUMBER,p_param_value number)
RETURN t_emp1
AS
V_t_emp1 t_emp1;
BEGIN
SELECT OBJ_emp1(emp_ID,vac_balance_day,value_parameter,net_vac)
BULK COLLECT INTO V_t_emp1
FROM(
SELECT e.emp_id, e.vac_balance_day, t.value_parameter,
get_vac_net (t.value_parameter, e.vac_balance_day) net_vac
FROM empl e,
TABLE
(params_tab
(params_typ (p_emp_id, p_param_value))) t
WHERE e.emp_id = t.emp_id
);
RETURN V_t_emp1;
EXCEPTION
WHEN OTHERS THEN
V_t_emp1.DELETE;
RETURN V_t_emp1;
END;
/
-- View
CREATE OR REPLACE FORCE VIEW v_info (emp_id,vac_balance_day,value_parameter,net_vac)
AS
select * from table( get_emp_vac(1,1))
union
select * from table( get_emp_vac(2,3))
/
select * from v_info
EMP_ID VAC_BALANCE_DAY VALUE_PARAMETER NET_VAC
---------- --------------- --------------- ----------
1 10 1 350
2 35 3 255
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here [message #533002 is a reply to message #532819] |
Sat, 26 November 2011 08:15 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
Thanks a lot Barbara ,
Yes,that's right,It would be much simpler just to use two tables
I will simple change in my scenario,
CREATE TABLE EMPL
(
EMP_ID NUMBER
)
Insert into EMPL
(EMP_ID)
Values
(1);
Insert into EMPL
(EMP_ID)
Values
(2);
Insert into EMPL
(EMP_ID)
Values
(3);
Insert into EMPL
(EMP_ID)
Values
(4);
Insert into EMPL
(EMP_ID)
Values
(5);
COMMIT;
CREATE TABLE EMPL_VAC
(
EMP_ID NUMBER,
LENGTH NUMBER,
NET_VAC NUMBER
)
SQL> SELECT * FROM EMPL
2 /
EMP_ID
----------
1
2
3
4
5
I have too use view to calculate net_vac
CREATE OR REPLACE VIEW V_EMPL (EMP_ID, LENGTH, NET_VAC)
AS
SELECT E1.EMP_ID, E2.LENGTH, (NVL (E2.LENGTH, 0) * 10)
FROM EMPL E1, EMPL_VAC E2
WHERE E1.EMP_ID = E2.EMP_ID(+);
SQL> SELECT * FROM V_EMPL
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 0
2 0
3 0
4 0
5 0
I was trying to do in old scenario is pass length as value_parmerter to view,
But now read from EMPL_VAC
ok
now I want when insert new record or update on EMPL_VAC retrive net_vac from a view (V_EMPL)
When insert status I wrote :
CREATE OR REPLACE PACKAGE STORE_HIS IS
TYPE T_HIS IS RECORD ( EMP_ID NUMBER, LENGTH NUMBER);
TYPE T_HIS_ARR IS TABLE OF T_HIS INDEX BY PLS_INTEGER;
HISUPD T_HIS_ARR;
TRIGINSERTS BOOLEAN := FALSE;
END;
/
CREATE OR REPLACE TRIGGER T_HIS_AR AFTER INSERT ON EMPL_VAC FOR EACH ROW
DECLARE
NEXTEL PLS_INTEGER;
BEGIN
IF NOT STORE_HIS.TRIGINSERTS THEN
NEXTEL := STORE_HIS.HISUPD.COUNT+1;
STORE_HIS.HISUPD(NEXTEL).EMP_ID := :NEW.EMP_ID;
STORE_HIS.HISUPD(NEXTEL).LENGTH := :NEW.LENGTH;
END IF;
END;
/
CREATE OR REPLACE TRIGGER T_HIS_BS BEFORE INSERT ON EMPL_VAC
BEGIN
IF NOT STORE_HIS.TRIGINSERTS THEN
STORE_HIS.HISUPD.DELETE;
END IF;
END;
/
CREATE OR REPLACE TRIGGER T_HIS_AS AFTER INSERT ON EMPL_VAC
BEGIN STORE_HIS.TRIGINSERTS := TRUE;
FOR I IN NVL(STORE_HIS.HISUPD.FIRST,1)..NVL(STORE_HIS.HISUPD.LAST,0) LOOP
FOR REC IN (SELECT * FROM V_EMPL
WHERE EMP_ID = STORE_HIS.HISUPD(I).EMP_ID
AND LENGTH = STORE_HIS.HISUPD(I).LENGTH
) LOOP
UPDATE EMPL_VAC
SET NET_VAC = REC.NET_VAC
WHERE EMP_ID = REC.EMP_ID
AND LENGTH = REC.LENGTH ;
END LOOP;
END LOOP;
STORE_HIS.TRIGINSERTS := FALSE;
END;
/
Insert into EMPL_VAC
(EMP_ID, LENGTH)
Values
(1, 1);
commit;
SQL> select * from empl_vac
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
SQL> SELECT * FROM V_EMPL
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
2 0
3 0
4 0
5 0
Insert into EMPL_VAC
(EMP_ID, LENGTH)
Values
(1, 2);
commit;
SQL> select * from empl_vac
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
1 2 20
SQL> SELECT * FROM V_EMPL
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
1 2 20
2 0
3 0
4 0
5 0
-----------------------
With insert it's ok ,
I want to
when update length in empl_vac retrive NET_VAC from view V_EMPL
ex:
update empl_vac
set length = 3
where emp_id = 1
and length = 1
Except output
select * from empl_vac
/
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 30
1 2 20
SELECT * FROM V_EMPL
/
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 30
1 2 20
2 0
3 0
4 0
5 0
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here [message #533005 is a reply to message #533002] |
Sat, 26 November 2011 12:16 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Either you are not stating the problem clearly or you are using an unnecessarily complicated roundabout way of accomplishing something simple. It usually helps if you describe, from a user standpoint, what you need, instead of describing from a programmer point of view what you think you need to accomplish it. It looks like you have two tables, one master and one detail, you want one column in the detail table to be calculated automatically, based on data from another column in the detail table, which can be done with a trigger, and you want to be able to view an outer join of the two tables, which can be done with a view. So, all you need, in addition to your tables, is a simple trigger and a simple view. Then, when you insert or update the length column, it will automatically calculate the net_vac column, which will be visible in both the table and the view. You do not need to pass a parameter to a view or use a collection or any other complicated method. Please see the simplified demonstration below. If this is not what you think you need, then please try to explain why and describe what your users need, not what you think you need to do to provide that.
-- you have two tables:
SCOTT@orcl_11gR2> CREATE TABLE empl
2 (emp_id NUMBER)
3 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO empl (emp_id) VALUES (1)
3 INTO empl (emp_id) VALUES (2)
4 INTO empl (emp_id) VALUES (3)
5 INTO empl (emp_id) VALUES (4)
6 INTO empl (emp_id) VALUES (5)
7 SELECT * FROM DUAL
8 /
5 rows created.
SCOTT@orcl_11gR2> CREATE TABLE empl_VAC
2 (emp_id NUMBER,
3 length NUMBER,
4 net_vac NUMBER)
5 /
Table created.
-- you want net_vac to be calculated automatically
-- when you insert or update length in empl_vac,
-- so you can do this with a trigger:
SCOTT@orcl_11gR2> CREATE OR REPLACE TRIGGER empl_vac_biur
2 BEFORE INSERT OR UPDATE ON empl_vac
3 FOR EACH ROW
4 BEGIN
5 :NEW.net_vac := NVL (:NEW.length, 0) * 10;
6 END empl_vac_biur;
7 /
Trigger created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
-- you want to be able to view an outer join of the tables,
-- so you can do this with a view:
SCOTT@orcl_11gR2> CREATE OR REPLACE VIEW v_empl
2 (emp_id, length, net_vac)
3 AS
4 SELECT e1.emp_id,
5 e2.length,
6 e2.net_vac
7 FROM empl e1, empl_vac e2
8 WHERE e1.emp_id = e2.emp_id(+)
9 /
View created.
-- test inserts and results:
SCOTT@orcl_11gR2> INSERT INTO empl_VAC (emp_id, length) VALUES (1, 1)
2 /
1 row created.
SCOTT@orcl_11gR2> SELECT * FROM empl_vac ORDER BY emp_id, length
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM V_empl ORDER BY emp_id, length
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
2
3
4
5
5 rows selected.
SCOTT@orcl_11gR2> INSERT INTO empl_VAC (emp_id, length) VALUES (1, 2)
2 /
1 row created.
SCOTT@orcl_11gR2> SELECT * FROM empl_vac ORDER BY emp_id, length
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
1 2 20
2 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM V_empl ORDER BY emp_id, length
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 1 10
1 2 20
2
3
4
5
6 rows selected.
-- test of update and results:
SCOTT@orcl_11gR2> UPDATE empl_vac
2 SET length = 3
3 WHERE emp_id = 1
4 AND length = 1
5 /
1 row updated.
SCOTT@orcl_11gR2> SELECT * FROM empl_vac ORDER BY emp_id, length
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 2 20
1 3 30
2 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM V_empl ORDER BY emp_id, length
2 /
EMP_ID LENGTH NET_VAC
---------- ---------- ----------
1 2 20
1 3 30
2
3
4
5
6 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533320 is a reply to message #532601] |
Tue, 29 November 2011 07:49 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
hi ,
I apologize for the delay
I will explain the real scenario,
I want to calculate PERIOD_START_DATE and PERIOD_END_DATE for every vaction
for employee if this vaction is vac_code = 1 only,,
PERIOD_START_DATE and PERIOD_END_DATE represents the period granted for every vaction,
I used table emp_vac to insert all vaction
Here i insert 3 a vaction for emp_id = 1 ,
2 vaction is vac_code = 1 (here only calculate period ) ,
and the third vac_code = 2 (no calculate period)....
When insert a vaction, calculate PERIOD_START_DATE and PERIOD_END_DATE
When change vac_length or start_date ,will recalculate PERIOD_START_DATE and PERIOD_END_DATE
I create some view, The reason for Link to between the table data automatically,
but not complete, I tried through calcalate period, but I can not completions,
please help me to calculate PERIOD,,
to calculate PERIOD START DATE :
*1. Get END_PERIOD_DT + (1 daye) for the last vaction for employee ..look on finction GET_PREVIOUS_VAC_PRIOD
-- if no any vaction for emplyee, then go the second step 2
*2. Get APPOINTMENT DATE for employee ..look on function GET_APPOINTMENT_DT
** To more the explin in attached
to calculate PERIOD END DATE :
Explin with attached
Kindly find the attached .
can look on the view
#3. The employee possibility is that more than the grade for during period of work
I get that from view , I used view,because need data dynamically
--look view v_emp_grade_start_dt & v_emp_grade_start_end
#4. dynamically compare for evry vaction(start_period_dt) with grade period step(#3)
-- I want to know the start_period_dt, that between grade period
-- look view v_emp_grade_start_end
#5. dynamically compare for grade period wuth table vac_property (grade&date)
-- look view v_compare_vac_prty_grde_period & v_filter_vac_prty_grde_period
CREATE TABLE EMP_HISTORY
(
EMP_ID NUMBER,
HIS_DATE DATE,
GRADE NUMBER
)
Insert into EMP_HISTORY
(EMP_ID, HIS_DATE, GRADE)
Values
(1, TO_DATE('01/01/2000', 'MM/DD/YYYY'), 1);
Insert into EMP_HISTORY
(EMP_ID, HIS_DATE, GRADE)
Values
(1, TO_DATE('05/09/2000', 'MM/DD/YYYY'), 2);
COMMIT;
----------------------------------
CREATE TABLE EMPL
(
EMP_ID NUMBER
)
Insert into EMPL
(EMP_ID)
Values
(1);
--------------------------------
CREATE TABLE EMP_VAC
(
EMP_ID NUMBER,
VAC_LENGTH NUMBER,
START_DT DATE,
END_DT DATE,
START_PERIOD_DT DATE,
END_PERIOD_DT DATE
)
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 10, TO_DATE('05/01/2002', 'MM/DD/YYYY'), TO_DATE('05/10/2002', 'MM/DD/YYYY'), NULL,
NULL, 1);
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 25, TO_DATE('02/01/2001', 'MM/DD/YYYY'), TO_DATE('02/25/2001', 'MM/DD/YYYY'), NULL,
NULL, 1);
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 5, TO_DATE('07/01/2000', 'MM/DD/YYYY'), TO_DATE('07/05/2000', 'MM/DD/YYYY'), NULL,
NULL, 2);
COMMIT;
--------------------------------
CREATE TABLE VAC_PROPERTY
(
GRADE_ID NUMBER NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE, --- if end_date is null , mean till now -- current date
PERIOD_DAY NUMBER
)
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(1, TO_DATE('01/25/1999', 'MM/DD/YYYY'), TO_DATE('03/20/2000', 'MM/DD/YYYY'), 11.25);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(1, TO_DATE('03/21/2000', 'MM/DD/YYYY'), NULL, 9.729);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(2, TO_DATE('01/01/2000', 'MM/DD/YYYY'), TO_DATE('02/10/2001', 'MM/DD/YYYY'), 10.9);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(2, TO_DATE('02/11/2001', 'MM/DD/YYYY'), NULL, 9);
COMMIT;
-----------------
CREATE OR REPLACE FUNCTION GET_PREVIOUS_VAC_PRIOD (P_EMP_ID NUMBER, P_START_DT DATE) RETURN DATE IS --*1
P_START DATE;
BEGIN
BEGIN
SELECT END_PERIOD_DT
INTO P_START
FROM EMP_VAC T1
WHERE T1.EMP_ID = P_EMP_ID
AND T1.START_DT IN ( SELECT MAX(T2.START_DT)
FROM EMP_VAC T2
WHERE T2.EMP_ID = P_EMP_ID
AND T2.START_DT < P_START_DT);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
IF P_START IS NULL THEN
RETURN P_START;
ELSE
P_START := P_START + 1;
RETURN P_START;
END IF;
END;
/
--------
CREATE OR REPLACE FUNCTION GET_PREVIOUS_VAC_START (P_EMP_ID NUMBER, P_START_DT DATE) RETURN DATE IS
P_START DATE;
BEGIN
BEGIN
SELECT START_DT
INTO P_START
FROM EMP_VAC T1
WHERE T1.EMP_ID = P_EMP_ID
AND T1.START_DT IN ( SELECT MAX(T2.START_DT)
FROM EMP_VAC T2
WHERE T2.EMP_ID = P_EMP_ID
AND T2.START_DT < P_START_DT);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN P_START;
END;
/
-----------------
CREATE OR REPLACE FUNCTION GET_APPOINTMENT_DT (P_EMP_ID NUMBER) RETURN DATE IS
P_START DATE;
BEGIN
BEGIN
SELECT HIS_DATE
INTO P_START
FROM EMP_HISTORY
WHERE EMP_ID = P_EMP_ID
AND HIS_TYPE = 1 --- type 1 mean APPOINTMENT -- start date in work
;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN P_START;
END;
/
------------------
CREATE OR REPLACE VIEW v_p1 (emp_id, start_dt, vac_length)
AS
SELECT e1.emp_id, start_dt, vac_length
FROM empL e1, EMP_VAC e2
WHERE e1.emp_id = e2.emp_id(+)
ORDER BY e1.emp_id;
-----------------
CREATE OR REPLACE VIEW v_p2 (emp_id,start_dt,vac_length, start_period_dt)
AS
SELECT emp_id, start_dt, vac_length,
NVL(get_previous_vac_priod (emp_id, start_dt),get_appointment_dt (emp_id))
FROM v_p1
ORDER BY emp_id, start_dt;
-------------------
-- get start date for every grade for employee
CREATE OR REPLACE VIEW v_emp_grade_start_dt (emp_id, grade,start_dt)
AS
SELECT DISTINCT emp_id, grade,
(SELECT MIN (his_date)
FROM emp_history h2
WHERE h2.emp_id = h1.emp_id
AND h2.grade = h1.grade) his_date
FROM emp_history h1
GROUP BY emp_id, grade
ORDER BY emp_id, his_date;
-------------------
-- get start & end date for every grade for employee
CREATE OR REPLACE VIEW v_emp_grade_start_end (emp_id,grade,start_date,end_date)
AS
SELECT DISTINCT emp_id, grade, start_dt,
(SELECT NVL (MIN (his_date), SYSDATE)
FROM emp_history h3
WHERE h3.emp_id = h1.emp_id
AND h3.his_date > h1.start_dt) end_date
FROM v_emp_grade_start_dt h1
ORDER BY emp_id, start_dt;
SQL> select * from v_emp_grade_start_end
2 /
---------------------------
CREATE OR REPLACE VIEW v_compare_vac_grde_period (emp_id,
grade,
start_date,
end_date,
start_period_dt
)
AS
SELECT v1.emp_id, v1.grade, v1.start_date, v1.end_date,
b.start_period_dt
FROM v_emp_grade_start_end v1, v_p2 b
WHERE v1.emp_id = b.emp_id AND b.start_period_dt < v1.end_date
ORDER BY v1.emp_id, v1.grade, b.start_period_dt;
---------------------------------
CREATE OR REPLACE VIEW v_compare_vac_prty_grde_period (
emp_id,grade,vac_length,start_date,end_date,start_period_dt,
bal_start_date,bal_end_date,period_day )
AS
SELECT v1.emp_id, v1.grade, vac_length, v1.start_date, v1.end_date,
v1.start_period_dt, b.start_date, b.end_date, period_day
FROM v_compare_vac_grde_period v1, vac_property b
WHERE b.grade_id = v1.grade AND b.start_date < v1.end_date
ORDER BY v1.emp_id, v1.grade, b.start_date;
------------------------
CREATE OR REPLACE FUNCTION FILTER_END_DATE
(P_EMP_ID IN NUMBER ,P_GRADE IN NUMBER,P_END_DATE IN DATE, P_BAL_DATE IN DATE)RETURN DATE IS
V_END_DATE DATE;
BEGIN
IF P_BAL_DATE <= P_END_DATE THEN
V_END_DATE := P_BAL_DATE ;
ELSIF P_BAL_DATE > P_END_DATE OR P_BAL_DATE IS NULL THEN
V_END_DATE := P_END_DATE;
END IF;
RETURN V_END_DATE;
END;
CREATE OR REPLACE FUNCTION FILTER_START_DATE
(P_EMP_ID IN NUMBER ,P_GRADE IN NUMBER,P_START_DATE IN DATE, P_BAL_DATE IN DATE) RETURN DATE IS
V_START_DATE DATE;
BEGIN
IF P_START_DATE >= P_BAL_DATE THEN
V_START_DATE := P_START_DATE;
ELSE
V_START_DATE := P_BAL_DATE;
END IF;
RETURN V_START_DATE;
END;
CREATE OR REPLACE VIEW v_filter_vac_prty_grde_period (emp_id, grade,vac_length,start_period_dt,
actial_start_date,actial_end_date,period_day )
AS
SELECT emp_id, grade, vac_length, start_period_dt,
filter_start_date (emp_id, grade, start_date, bal_start_date),
filter_end_date (emp_id, grade, end_date, bal_end_date),
period_day
FROM v_compare_vac_prty_grde_period
ORDER BY emp_id,GRADE,START_PERIOD_DT;
-------------------------------------------------------------
Except output
select * from emp_vac
/
EMP_ID VAC_LENGTH START_DT END_DT START_PERIOD_DT END_PERIOD_DT VAC_CODE
------ --------- --------- -------- --------------- --------------- -------
1 5 2000/07/01 2000/07/05 2
1 25 2001/02/01 2001/02/25 2000/01/01 2000/11/03 1
1 10 2002/05/01 2002/05/10 2000/11/04 2001/02/19 1
WHEN UPDATE VAC_LENGTH OR START_DT , WILL UPDATE ON START_PERIOD_DT , END_PERIOD_DT
maybe change start_dt for first vaction to 2001/02/05
or change vac_length 20 and end_date to 2001/02/20
or cnange vac_length 25 and start_date to 2001/02/10
Thank you very very much in advance
[Updated on: Tue, 29 November 2011 07:58] Report message to a moderator |
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533324 is a reply to message #533320] |
Tue, 29 November 2011 07:59 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
Kindly find the attached for the second vaction
add table
CREATE TABLE TEST.EMP_TRANSACTION
(
EMP_ID NUMBER,
START_DT DATE,
END_DT DATE
)
Insert into EMP_TRANSACTION
(EMP_ID, START_DT, END_DT)
Values
(1, TO_DATE('02/01/2000', 'MM/DD/YYYY'), TO_DATE('02/18/2000', 'MM/DD/YYYY'));
Insert into TEST.EMP_TRANSACTION
(EMP_ID, START_DT, END_DT)
Values
(1, TO_DATE('09/14/2000', 'MM/DD/YYYY'), TO_DATE('09/28/2000', 'MM/DD/YYYY'));
COMMIT;
SQL> select * from EMP_TRANSACTION
2 /
EMP_ID START_DT END_DT
---------- ----------------------- -----------------------
1 2000/02/01 2000/02/18
1 2000/09/14 2000/09/28
-----------------------
for test data
select * from emp_vac
/
EMP_ID VAC_LENGTH START_DT END_DT START_PERIOD_DT END_PERIOD_DT VAC_CODE
------ --------- --------- -------- --------------- --------------- -------
1 5 2000/07/01 2000/07/05 2
1 25 2001/02/01 2001/02/25 1
1 10 2002/05/01 2002/05/10 1
--------------------------
SQL> select * from EMP_HISTORY
2 /
EMP_ID HIS_DATE GRADE
---------- ----------------------- ----------
1 2000/01/01 1
1 2000/05/09 2
SQL> select * from VAC_PROPERTY
2 /
GRADE_ID START_DATE END_DATE PERIOD_DAY
---------- ----------------------- ----------------------- ----------
1 1999/01/25 2000/03/20 11.25
1 2000/03/21 9.729
2 2000/01/01 2001/02/10 10.9
2 2001/02/11 9
SQL> select * from EMPL
2 /
EMP_ID
----------
1
[Updated on: Tue, 29 November 2011 08:50] Report message to a moderator |
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533397 is a reply to message #533324] |
Tue, 29 November 2011 19:01 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It is very disappointing to find that the last week was wasted on something that was not the real problem. Your new code is full of errors. I cannot read your jpg files. Your explanations are incomprehensible. You need to test your code before posting it and make sure that all tables create, all inserts work, and so forth. You need to post the script separate from explanation, unless you include the explanation as comments prefaced by --. You need to post all explanation as part of the post, not as an attachment, stated as clearly and simply as possible with proper English. You need to provide a simple, "when the user does this ... I want this ... to happen" showing before and after. When I see things like "except output" I don't know if that means everything is fine except that this is the output you are getting or this is the expected output or what. If it is something as simple as, "when a user updates column a or column b, then I want column c and column d to be updated in the following manner ..., so that if the starting data is ... and the following inserts and updates are issued, then the result should be ..." then say that.
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533488 is a reply to message #533398] |
Wed, 30 November 2011 04:06 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
I means when insert columns values(EMP_ID,VAC_LENGTH,START_DT,END_DT,VAC_CODE) into table (EMP_VAC),
or update columns values(VAC_LENGTH,START_DT,END_DT) ,
Will be returned columns values(START_PERIOD_DT,END_PERIOD_DT) .
This is what I was trying to do last week,
send or pass columns values (EMP_ID,VAC_LENGTH,START_DT,END_DT,VAC_CODE) ​​to the views ,
and return columns values(START_PERIOD_DT,END_PERIOD_DT) to same table (EMP_VAC).
I used a series of view for easy linking between the tables(EMP_VAC,EMP_HISTORY,EMP_TRANSACTION,VAC_PROPERTY).
This series of the view depends on columns values(EMP_ID,VAC_LENGTH,START_DT,END_DT,VAC_CODE)
when insert or update from table(EMP_VAC),
and processing of such columns values(EMP_ID,VAC_LENGTH,START_DT,END_DT,VAC_CODE) by linking
with other tables (EMP_VAC,EMP_HISTORY,EMP_TRANSACTION,VAC_PROPERTY)
When insert columns values(EMP_ID,VAC_LENGTH,START_DT,END_DT,VAC_CODE) into table (EMP_VAC),
or update columns values(VAC_LENGTH,START_DT,END_DT) ,
Will be calculate columns values(START_PERIOD_DT,END_PERIOD_DT)
Steps calculate column value (START_PERIOD_DT) in table (EMP_VAC) :
1. when insert columns values(EMP_ID,VAC_LENGTH,START_DT,END_DT,VAC_CODE) into table (EMP_VAC)
or update columns values(START_PERIOD_DT,END_PERIOD_DT) , As follows :
I see ,
A. If this First vaction(First record) of EMP_ID and VAC_CODE = 1 THEN
START_PERIOD_DT = take date for start work ,
See function below GET_APPOINTMENT_DT , return START_PERIOD_DT
CREATE OR REPLACE FUNCTION GET_APPOINTMENT_DT (P_EMP_ID NUMBER) RETURN DATE IS
P_START DATE;
BEGIN
BEGIN
SELECT min(HIS_DATE)
INTO P_START
FROM EMP_HISTORY
WHERE EMP_ID = P_EMP_ID
;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN P_START;
END;
/
B. If this not First vaction(First record) of EMP_ID and VAC_CODE = 1 THEN
START_PERIOD_DT = take value(END_PERIOD_DT) + 1 day from table (EMP_VAC)
See function below GET_PREVIOUS_VAC_START , return START_PERIOD_DT
CREATE OR REPLACE FUNCTION GET_PREVIOUS_VAC_START (P_EMP_ID NUMBER, P_START_DT DATE) RETURN DATE IS
P_START DATE;
BEGIN
BEGIN
SELECT END_PERIOD_DT + 1
INTO P_START
FROM EMP_VAC T1
WHERE T1.EMP_ID = P_EMP_ID
AND VAC_CODE = 1
AND T1.START_DT IN ( SELECT MAX(T2.START_DT)
FROM EMP_VAC T2
WHERE T2.EMP_ID = P_EMP_ID
AND VAC_CODE = 1
AND T2.START_DT < P_START_DT);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN P_START;
END;
/
------------------------------------------------------
Steps calculate column value(END_PERIOD_DT) in table (EMP_VAC) :
1. after calculate START_PERIOD_DT
take START_PERIOD_DT , and see START_PERIOD_DT under any grade for
emp_id from table (EMP_HISTORY).
See view below (v_emp_grade_start_dt) & (v_emp_grade_start_end) ,
You can know (start date) and (end date) of each degree of any emp_id by view(v_emp_grade_start_end) ,
CREATE OR REPLACE VIEW v_emp_grade_start_dt (emp_id, grade,start_dt)
AS
SELECT DISTINCT emp_id, grade,
(SELECT MIN (his_date)
FROM emp_history h2
WHERE h2.emp_id = h1.emp_id
AND h2.grade = h1.grade) his_date
FROM emp_history h1
GROUP BY emp_id, grade
ORDER BY emp_id, his_date;
-------------------
-- get start & end date for every grade for employee
CREATE OR REPLACE VIEW v_emp_grade_start_end (emp_id,grade,start_date,end_date)
AS
SELECT DISTINCT emp_id, grade, start_dt,
(SELECT NVL (MIN (his_date), SYSDATE)
FROM emp_history h3
WHERE h3.emp_id = h1.emp_id
AND h3.his_date > h1.start_dt) end_date
FROM v_emp_grade_start_dt h1
ORDER BY emp_id, start_dt;
I want from this step know grade depends on (emp_id) & (START_PERIOD_DT).
2.after know (grade) & (START_PERIOD_DT)
I want know (period_day) form table (vac_property) for (grade) & (START_PERIOD_DT)
See select below
After know (period_day) calculate (END_PERIOD_DT) :
START_PERIOD_DT add number of days result(vac_length * period_day) As follows :
END_PERIOD_DT = START_PERIOD_DT + ( (vac_length * period_day) )
SELECT PERIOD_DAY
FROM VAC_PROPERTY
WHERE GRADE_ID = &grade
AND START_DATE <= &START_PERIOD_DT
AND (END_DATE >= &START_PERIOD_DT)
OR END_DATE IS NULL) --when end_date is null means end_date = (sysdate) day date
3.after know (START_PERIOD_DT) & (END_PERIOD_DT)
I want know all grade for emp_id from table (EMP_HISTORY) where between (START_PERIOD_DT) & (END_PERIOD_DT)
To find that can use VIEW v_emp_grade_start_end, to know all garde for emp_id
maybe emp_id between (START_PERIOD_DT) & (END_PERIOD_DT) have more grade
Then , I want know (period_day) every grade, maybe grade is divided more than one period,
**** FOR the explain more and complete the rest of the steps see attched,
**** I insert two vaction for emp_id = 1 , can see explian of attched ,
How to calculate (START_PERIOD_DT) & (END_PERIOD_DT) if Insert this statment :
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 10, TO_DATE('2002-05-01', 'YYYY-MM-DD'), TO_DATE('2002-05-10', 'YYYY-MM-DD'), NULL,
NULL, 1);
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 25, TO_DATE('001-02-02', 'YYYY-MM-DD'), TO_DATE('2001-02-25', 'YYYY-MM-DD'), NULL,
NULL, 1);
-------------recreate script-------------------
CREATE TABLE VAC_PROPERTY
(
GRADE_ID NUMBER NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE,
PERIOD_DAY NUMBER
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(1, TO_DATE('2000-03-21', 'YYYY-MM-DD'), NULL, 9.729);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(2, TO_DATE('2000-01-01', 'YYYY-MM-DD'), TO_DATE('2001-02-10', 'YYYY-MM-DD'), 10.9);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(2, TO_DATE('2001-02-11', 'YYYY-MM-DD'), NULL, 9);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(1, TO_DATE('1998-01-25', 'YYYY-MM-DD'), TO_DATE('2000-03-20', 'YYYY-MM-DD'), 11.25);
COMMIT;
----------------------------
CREATE TABLE EMP_VAC
(
EMP_ID NUMBER,
VAC_LENGTH NUMBER,
START_DT DATE,
END_DT DATE,
START_PERIOD_DT DATE,
END_PERIOD_DT DATE,
VAC_CODE NUMBER(1)
)
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 5, TO_DATE('2000-07-01', 'YYYY-MM-DD'), TO_DATE('2000-07-05', 'YYYY-MM-DD'), NULL,
NULL, 2);
COMMIT;
----------------------
CREATE TABLE EMP_TRANSACTION
(
EMP_ID NUMBER,
START_DT DATE,
END_DT DATE
)
Insert into EMP_TRANSACTION
(EMP_ID, START_DT, END_DT)
Values
(1, TO_DATE('2000-02-01', 'YYYY-MM-DD'), TO_DATE('2000-02-18', 'YYYY-MM-DD'));
Insert into EMP_TRANSACTION
(EMP_ID, START_DT, END_DT)
Values
(1, TO_DATE('2000-09-14', 'YYYY-MM-DD'), TO_DATE('2000-09-28', 'YYYY-MM-DD'));
COMMIT;
----------------------
CREATE TABLE TEST.EMP_HISTORY
(
EMP_ID NUMBER,
HIS_DATE DATE,
GRADE NUMBER(1)
)
Insert into EMP_HISTORY
(EMP_ID, HIS_DATE, GRADE)
Values
(1, TO_DATE('2000-01-01', 'YYYY-MM-DD'), 1);
Insert into EMP_HISTORY
(EMP_ID, HIS_DATE, GRADE)
Values
(1, TO_DATE('2000-05-09', 'YYYY-MM-DD'), 2);
COMMIT;
----------------------
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533491 is a reply to message #533488] |
Wed, 30 November 2011 04:17 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Your create script still doesn't work:
SQL> CREATE TABLE VAC_PROPERTY
2 (
3 GRADE_ID NUMBER NOT NULL,
4 START_DATE DATE NOT NULL,
5 END_DATE DATE,
6 PERIOD_DAY NUMBER
7
SQL>
SQL> Insert into VAC_PROPERTY
2 (GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
3 Values
4 (1, TO_DATE('2000-03-21', 'YYYY-MM-DD'), NULL, 9.729);
Insert into VAC_PROPERTY
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
If you're too lazy to test it, why should we put effort into helping you?
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533505 is a reply to message #533502] |
Wed, 30 November 2011 05:18 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ORGDN wrote on Wed, 30 November 2011 11:11cookiemonster wrote on Wed, 30 November 2011 04:17Your create script still doesn't work:
SQL> CREATE TABLE VAC_PROPERTY
2 (
3 GRADE_ID NUMBER NOT NULL,
4 START_DATE DATE NOT NULL,
5 END_DATE DATE,
6 PERIOD_DAY NUMBER
7
SQL>
Seems you not closed arc "(" in line 7
You didn't close the bracket. That's a copy and paste of your code from the post above.
You haven't tested it. It doesn't work.
That was my point.
Test it. Make sure it works. Repost it when it does.
|
|
|
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533517 is a reply to message #533513] |
Wed, 30 November 2011 06:18 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
ORGDN wrote on Wed, 30 November 2011 03:46
I explain more within the attached file for each step , and giving an example of two vacation for the same employee,
Many people cannot download files and you cannot copy and paste from a jpg. This is why you should provide everything within the the thread, not as an attachment. As previously stated, I cannot read your jpg attachments. The letters are too small and when I make them big enough, they become too fuzzy, due to resolution. So, repeatedly referring me to something that cannot be read for explanation is useless.
ORGDN wrote on Wed, 30 November 2011 03:46
"take date for start work" means first day of work for the employee
So, what has the first day of work got to do with vacation dates?
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533535 is a reply to message #533513] |
Wed, 30 November 2011 08:03 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
ORGDN wrote on Wed, 30 November 2011 06:46I explain more within the attached file for each step , and giving an example of two vacation for the same employee,
"take date for start work" means first day of work for the employee
and his_date existing in table EMP_HISTORY
CREATE TABLE EMP_HISTORY
(
EMP_ID NUMBER,
HIS_DATE DATE,
GRADE NUMBER(1)
)
SQL> CREATE TABLE VAC_PROPERTY
2 (
3 GRADE_ID NUMBER NOT NULL,
4 START_DATE DATE NOT NULL,
5 END_DATE DATE,
6 PERIOD_DAY NUMBER
7 )
Still no good. I cut and pasted what you posted:
SQL> SQL> CREATE TABLE VAC_PROPERTY
2 (
3 GRADE_ID NUMBER NOT NULL,
4 START_DATE DATE NOT NULL,
5 END_DATE DATE,
6 PERIOD_DAY NUMBER
7 )SP2-0734: unknown command beginning "SQL> CREAT..." - rest of line ignored.
SQL> SQL> SQL> SQL> SQL> SQL>
Cut and paste what you posted. When it runs properly for you, ONLY THEN do you post it here. We're all really tired of you not knowing how to post a proper message.
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533611 is a reply to message #533517] |
Wed, 30 November 2011 18:21 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
---------------------create script----------------------------
CREATE TABLE EMPL
(
EMP_ID NUMBER
)
Insert into EMPL
(EMP_ID)
Values
(1);
-------
CREATE TABLE VAC_PROPERTY
(
GRADE_ID NUMBER NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE,
PERIOD_DAY NUMBER
)
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(1, TO_DATE('2000-03-21', 'YYYY-MM-DD'), NULL, 9.729);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(2, TO_DATE('2000-01-01', 'YYYY-MM-DD'), TO_DATE('2001-02-10', 'YYYY-MM-DD'), 10.9);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(2, TO_DATE('2001-02-11', 'YYYY-MM-DD'), NULL, 9);
Insert into VAC_PROPERTY
(GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
Values
(1, TO_DATE('1998-01-25', 'YYYY-MM-DD'), TO_DATE('2000-03-20', 'YYYY-MM-DD'), 11.25);
COMMIT;
----------------------------
CREATE TABLE EMP_VAC
(
EMP_ID NUMBER,
VAC_LENGTH NUMBER,
START_DT DATE,
END_DT DATE,
START_PERIOD_DT DATE,
END_PERIOD_DT DATE,
VAC_CODE NUMBER(1)
)
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 5, TO_DATE('2000-07-01', 'YYYY-MM-DD'), TO_DATE('2000-07-05', 'YYYY-MM-DD'), NULL,
NULL, 2);
COMMIT;
----------------------
CREATE TABLE EMP_TRANSACTION
(
EMP_ID NUMBER,
START_DT DATE,
END_DT DATE
)
Insert into EMP_TRANSACTION
(EMP_ID, START_DT, END_DT)
Values
(1, TO_DATE('2000-02-01', 'YYYY-MM-DD'), TO_DATE('2000-02-18', 'YYYY-MM-DD'));
Insert into EMP_TRANSACTION
(EMP_ID, START_DT, END_DT)
Values
(1, TO_DATE('2000-09-14', 'YYYY-MM-DD'), TO_DATE('2000-09-28', 'YYYY-MM-DD'));
COMMIT;
----------------------
CREATE TABLE EMP_HISTORY
(
EMP_ID NUMBER,
HIS_DATE DATE,
GRADE NUMBER(1)
)
Insert into EMP_HISTORY
(EMP_ID, HIS_DATE, GRADE)
Values
(1, TO_DATE('2000-01-01', 'YYYY-MM-DD'), 1);
Insert into EMP_HISTORY
(EMP_ID, HIS_DATE, GRADE)
Values
(1, TO_DATE('2000-05-09', 'YYYY-MM-DD'), 2);
COMMIT;
----------------------
CREATE OR REPLACE FUNCTION GET_APPOINTMENT_DT (P_EMP_ID NUMBER) RETURN DATE IS
P_START DATE;
BEGIN
BEGIN
SELECT min(HIS_DATE)
INTO P_START
FROM EMP_HISTORY
WHERE EMP_ID = P_EMP_ID
;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN P_START;
END;
/
CREATE OR REPLACE FUNCTION GET_PREVIOUS_VAC_START (P_EMP_ID NUMBER, P_START_DT DATE) RETURN DATE IS
P_START DATE;
BEGIN
BEGIN
SELECT END_PERIOD_DT + 1
INTO P_START
FROM EMP_VAC T1
WHERE T1.EMP_ID = P_EMP_ID
AND VAC_CODE = 1
AND T1.START_DT IN ( SELECT MAX(T2.START_DT)
FROM EMP_VAC T2
WHERE T2.EMP_ID = P_EMP_ID
AND VAC_CODE = 1
AND T2.START_DT < P_START_DT);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN P_START;
END;
/
CREATE OR REPLACE VIEW v_emp_grade_start_dt (emp_id, grade,start_dt)
AS
SELECT DISTINCT emp_id, grade,
(SELECT MIN (his_date)
FROM emp_history h2
WHERE h2.emp_id = h1.emp_id
AND h2.grade = h1.grade) his_date
FROM emp_history h1
GROUP BY emp_id, grade
ORDER BY emp_id, his_date;
CREATE OR REPLACE VIEW v_emp_grade_start_end (emp_id,grade,start_date,end_date)
AS
SELECT DISTINCT emp_id, grade, start_dt,
(SELECT NVL (MIN (his_date-1), SYSDATE)
FROM emp_history h3
WHERE h3.emp_id = h1.emp_id
AND h3.his_date > h1.start_dt) end_date
FROM v_emp_grade_start_dt h1
ORDER BY emp_id, start_dt;
Initial explain :
Assume or arguably the number of days of the year 360 days
Employee is entitled to take each year during the 30 days vacation,
every 1 year , can the employee taking 30 days vaction
When dividing the number of days of the year to 30 (360 / 30) = 12 days( this is (period day) )
Means every 12 days, can the employee taking the 1 day vaction
ex:
Assume the employee first day of work in 2000-01-01 ,
and the employee take first vaction ( start_date 2001-02-01 & end_date 2001-02-30 ) ,
means take 30 days vaction(vac_length),
I want to know this vaction( 30 days ) for any period accrued ( Can say the instead of the period)
I take (vacation length * period day ) = 30 * 12 = 360 days ,
360 days add to first day of work in (2000-01-01 + 360 days) = 2000-12-26, Then
this vaction, period accrued between 2000-01-01 and 2000-12-26, then
start_period_date = 2000-01-01 ,, end_period_date = 2000-12-26 .
-------------------------
and the employee take second vaction ( start_date 2001-05-01 & end_date 2001-05-15 ) ,
means take 15days vaction(vac_length),
I want to know this vaction( 15 days ) for any period accrued
I take (vacation length * period day ) = 15 * 12 = 180 days ,
180 days add to (next day for end_period_date for previous vaction )
then 2000-12-26 + 1 day= 2000-12-27 (this is next day)
(2000-12-27 + 180 days) = 2001-06-25, Then
this vaction, period accrued between 2000-12-27 and 2001-06-25,
start_period_date = 2000-12-27 ,, end_period_date = 2001-06-25 .
-------------------------
and the employee take third vaction ( start_date 2002-03-01 & end_date 2002-03-02 ) ,
means take 15days vaction(vac_length),
I want to know this vaction( 1 days ) for any period accrued
I take (vacation length * period day ) = 1 * 12 = 12 days ,
12 days add to (next day for end_period_date for previous vaction )
then 2001-06-25 + 1 day= 2001-06-26 (this is next day)
(2001-06-26 + 12)) = 2001-07-08, Then
this vaction, period accrued between 2001-06-26 and 2001-07-08,
start_period_date = 2001-06-26 ,, end_period_date = 2001-07-08 .
-----------------------------------
MORE EXPLAIN :
I create table VAC_PROPERTY for VALUE (period day) beacuse this period day diffrent from date to date
or from grade to grade
SQL> select * from VAC_PROPERTY
2 /
GRADE_ID START_DATE END_DATE PERIOD_DAY
---------- ----------------------- ----------------------- ----------
1 1999/01/25 2000/03/20 11.25
1 2000/03/21 9.729
2 2000/01/01 2001/02/10 10.9
2 2001/02/11 9
-- * if end date is null, means till now (today date)
------------------------------------------------------------------------
The emp_id = 1 when take first vaction ( start_date 2001/02/01 & end_date 2001/02/25 ) ,
vac_length = 25 , and vac_code = 1 ,
First step : calulate start_period_dt
because this first vaction,I take first day of work in 2000/01/01
start_period_dt = 2000/01/01, I can take from :
SELECT min(HIS_DATE)
FROM EMP_HISTORY
WHERE EMP_ID = 1
;
Second step : calulate end_period_dt
calculate: end_period_dt = start_period_dt + (vacation length * period day ) ,
but now , I don't know any period_day , use table VAC_PROPERTY to know that
take START_period_dt , and see emp_id = 1 under any grade from table (EMP_HISTORY):
SELECT *
FROM v_emp_grade_start_end
WHERE EMP_ID = 1
AND START_DATE <= to_date('2000-01-01','YYYY-MM-DD')
AND END_DATE >= to_date('2000-01-01','YYYY-MM-DD')
/
EMP_ID GRADE START_DAT END_DATE
---------- ---------- ---------- ----------
1 1 2000-01-01 2000-05-08
emp_id 1 under grade 1, I want to know PERIOD_DAY where grade 1 and date 2000-01-01
SELECT *
FROM VAC_PROPERTY
WHERE GRADE_ID = 1
ORDER BY START_DATE
/
GRADE_ID START_DAT END_DATE PERIOD_DAY
---------- --------- --------- ----------
1 1998-01-25 2000-03-20 11.25
1 2000-03-21 9.729
Obviously will be used PERIOD_DAY = 11.25 , because 2000-01-01 between 1998-01-25 and 2000-03-20
ok now
end_period_dt = start_period_dt + (vacation length * period day )
end_period_dt = 2000-01-01 + (25*11.25)
end_period_dt = 2000-01-01 + 281 days
*end_period_dt = 2000-10-08
Third step: i want to recheck this period under any grade for emp_id between
start_period_dt 2000-01-01 and end_period_dt = 2000-10-08
SELECT *
FROM v_emp_grade_start_end
WHERE EMP_ID = 1
AND START_DATE <= to_date('2000-10-08','YYYY-MM-DD')
AND END_DATE >= to_date('2000-01-01','YYYY-MM-DD')
/
EMP_ID GRADE START_DAT END_DATE
---------- ---------- ---------- ----------
1 1 2000-01-01 2000-05-08
1 2 2000-05-09 2011-11-03
I have two grade 1 , 2 ,,
*Must recalculate end_period_dt, because become have 2 grade between start_period_dt 2000-01-01 and
end_period_dt = 2000-10-08 .
I want to know PERIOD_DAY every grade :
a.grade 1 and between 2000-01-01 , 2000-05-08
b.grade 2 and between 2000-05-09 , 2011-11-03
a.grade 1 and between 2000-01-01 , 2000-05-08
SELECT GRADE_ID,PERIOD_DAY, START_DATE,nvl(END_DATE,to_date('2000-05-08','YYYY-MM-DD')) END_DATE
FROM VAC_PROPERTY
WHERE GRADE_ID = 1
AND START_DATE <= to_date('2000-05-08','YYYY-MM-DD')
AND (END_DATE >= to_date('2000-01-01','YYYY-MM-DD')
OR END_DATE IS NULL)
ORDER BY START_DATE
/
GRADE_ID PERIOD_DAY START_DAT END_DATE
---------- ---------- --------- -----------
1 11.25 1998-01-25 2000-03-20
1 9.729 2000-03-21 2000-05-09
now, ** I want compare filter PERIOD(start_period_dt 2000-01-01 and end_period_dt = 2000-10-08 ) with
the result PERIOD
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 1998-01-25 2000-03-20 2000-03-21 2000-05-08 + PERIOD VAC_PROPERTY
+ ** ** +
+ |...........||>>>>>>>>>>>>>>>>>>>>>>>>>>||>>>>>>>>>>>>>>>>>>>>|| +
+ +
+ 2000-01-01 2000-03-20 2000-03-21 2000-10-08 +PERIOD BETWEEN(
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
start_period_dt 2000-01-01 and end_period_dt = 2000-10-08)
except result after compare
GRADE_ID PERIOD_DAY START_DAT END_DATE DIFFERENCE_DAYS
---------- ---------- --------- ----------- ---------------
1 11.25 2000-01-01 2000-03-20 79
1 9.729 2000-03-21 2000-05-08 48
* END_DATE 2000-05-09 BECAUSE GRADE 1 FINISH HERE
b.grade 2 and between 2000-05-09 , 2011-11-03
SELECT GRADE_ID,PERIOD_DAY, START_DATE,nvl(END_DATE,to_date('2011-11-03','YYYY-MM-DD')) END_DATE
FROM VAC_PROPERTY
WHERE GRADE_ID = 2
AND START_DATE <= to_date('2011-11-03','YYYY-MM-DD')
AND (END_DATE >= to_date('2000-05-09','YYYY-MM-DD')
OR END_DATE IS NULL)
ORDER BY START_DATE
/
GRADE_ID PERIOD_DAY START_DAT END_DATE
---------- ---------- --------- ---------
2 10.9 2000-01-01 2001-02-10
2 9 2001-02-11 2011-11-30
now, **I want compare filter PERIOD (2000-05-08 + 1 DAY(NEXT DAY) = 2000-05-09 and end_period_dt = 2000-10-08 ) with
the result PERIOD
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 2000-01-01 2001-02-10 2001-02-10 2011-11-30 + PERIOD VAC_PROPERTY
+ ** +
+ |...........||>>>>>>>>>>>>>>>>>>>>>||.......|....................|| +
+ +
+ # 2000-05-09 2000-10-08 +PERIOD BETWEEN ( 2000-05-10
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++and end_period_dt= 2000-10-08)
except result after compare
GRADE_ID PERIOD_DAY START_DATE END_DATE DIFFERENCE_DAYS
---------- ---------- --------- ----------- ---------------
2 10.9 2000-05-09 2000-10-08 152
#START_DATE = 2000-05-10 BECAUSE NEXT DAY FOR FINISH END_DATE 2000-05-09 FOR GARDE 1
*END_DATE = 2000-10-08 BECAUSE end_period_dt = 2000-10-08 The end of the comparison
* And now I have three record become
GRADE_ID PERIOD_DAY START_DAT END_DATE DIFFERENCE_DAYS
---------- ---------- --------- ----------- ---------------
1 11.25 2000-01-01 2000-03-20 79
1 9.729 2000-03-21 2000-05-08 48
2 10.9 2000-05-09 2000-10-08 152
( 79 + 48 + 152 ) = 269 days
recalculate end_period_dt whith new 269 days
end_period_dt = 2000-01-01 + 269 days
*end_period_dt = 2000-09-26
fourth step :
check any period in table emp_transaction match between start_period_date = 2000/01/01 and end_period_dt = 2000-09-26
I take sum days for this period and add to end_date_period
SELECT SUM((END_DT - START_DT) + 1) TOTAL_DAYS
FROM EMP_TRANSACTION
WHERE EMP_ID = 1
AND START_DT <= to_date('2000-09-26','YYYY-MM-DD')
AND END_DT >= to_date('2000/01/01','YYYY-MM-DD')
/
TOTAL_DAYS
----------
33
recalculate end_period_dt whith 33 days
end_period_dt = 2000-01-01 + 33 days
*end_period_dt = 2000-10-29
Fifth step :
check any period in table emp_vac where vac_code = 2 match between start_period_date = 2000/01/01
and end_period_dt = 2000-09-26
SQL> SELECT SUM((END_DT - START_DT) + 1) TOTAL_DAYS
2 FROM EMP_VAC
3 WHERE EMP_ID = 1
4 AND VAC_CODE = 2
5 AND START_DT <= to_date('2000-09-26','YYYY-MM-DD')
6 AND END_DT >= to_date('2000/01/01','YYYY-MM-DD')
7 /
TOTAL_DAYS
----------
5
recalculate end_period_dt whith 5 days
end_period_dt = 2000-10-29 + 5 days
*end_period_dt = 2000-11-03
When execute INSERT STATMENT ,
I WANT TO :
pass to_date('2000-01-01','YYYY-MM-DD') to column START_PERIOD_DT, and
pass to_date('2000-11-03','YYYY-MM-DD') to column END_PERIOD_DT :
Insert into EMP_VAC
(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,
END_PERIOD_DT, VAC_CODE)
Values
(1, 25, TO_DATE('2001-02-01', 'YYYY-MM-DD'), TO_DATE('2001-02-25', 'YYYY-MM-DD'), NULL,
NULL, 1);
EXCEPT OUTPUT :
SQL> SELECT * FROM EMP_VAC WHERE EMP_ID = 1 AND VAC_CODE = 1
2 /
EMP_ID VAC_LENGTH START_DT END_DT START_PERIOD_DT END_PERIO VAC_CODE
---------- ---------- --------- --------- --------------- --------- ----------
1 25 2001-02-01 2001-02-25 2000-01-01 2000-11-03 1
when update column VAC_LENGTH , START_DT repass new values to column START_PERIOD_DT and END_PERIOD_DT,Then
Take same the steps above.
----------------------------------------------------------------------------------------------------
Now , second vaction for emp_id = 1 and vac_code = 1
start_date 2002-05-01 & end_date 2002-05-10 ,
vac_length = 10 days , and vac_code = 1 ,
The same steps above, but the difference in calculate start_period_dt.
first step : calulate start_period_dt
start_period_dt = make next day for end_period_dt for prevoius vaction
* can use fuction GET_PREVIOUS_VAC_PRIOD (emp_id,START_DT)
select GET_PREVIOUS_VAC_PRIOD (1,to_date('2002-05-01','YYYY-MM-DD')) START_PERIOD_DT from dual
/
START_PERIOD_DT
---------------
2000-11-04
second step : calulate end_period_dt
calc: end_period_dt = start_period_dt + (vacation length * period day ) , but now , I don't know any period_day to calculate
from table VAC_PROPERTY
take START_period_dt , and see emp_id = 1 under any grade from table (EMP_HISTORY):
SELECT *
FROM v_emp_grade_start_end
WHERE EMP_ID = 1
AND START_DATE <= to_date('2002-05-01','YYYY-MM-DD')
AND END_DATE >= to_date('2002-05-01','YYYY-MM-DD')
/
EMP_ID GRADE START_DAT END_DATE
---------- ---------- ---------- ----------
1 2 2000-05-09 2011-11-03
emp_id 1 under grade 2, I want to know PERIOD_DAY where grade 2 and between 2000-05-09
can check that by table VAC_PROPERTY
SELECT *
FROM VAC_PROPERTY
WHERE GRADE_ID = 2
/
GRADE_ID START_DAT END_DATE PERIOD_DAY
---------- --------- --------- ----------
2 2000-01-01 2001-02-10 10.9
2 2001-02-11 9
Obviously will be used PERIOD_DAY = 10.9 , because 2000-05-09 between 2000-01-01 and 2001-02-10
ok now
end_period_dt = start_period_dt + (vacation length * period day )
end_period_dt = 2000-11-04 + (10*10.9)
end_period_dt = 2000-11-04 + 109 days
*end_period_dt = 2001-02-21
Third step: i want to recheck this period under any grade for emp_id between
start_period_dt 2000-11-04 and end_period_dt = 2001-02-21
SQL> SELECT *
2 FROM v_emp_grade_start_end
3 WHERE EMP_ID = 1
4 AND START_DATE <= to_date('2001-02-21','YYYY-MM-DD')
5 AND END_DATE >= to_date('2000-11-04','YYYY-MM-DD')
6 /
EMP_ID GRADE START_DAT END_DATE
---------- ---------- --------- ---------
1 2 2000-05-09 2011-11-30
[/code]
I have one grade 2 ,,
I want to know PERIOD_DAY for grade 2 between 2000-11-04 and 2001-02-21
SELECT GRADE_ID,PERIOD_DAY, START_DATE,nvl(END_DATE,to_date('2001-02-21','YYYY-MM-DD')) END_DATE
FROM VAC_PROPERTY
WHERE GRADE_ID = 2
AND START_DATE <= to_date('2001-02-21','YYYY-MM-DD')
AND (END_DATE >= to_date('2000-11-04','YYYY-MM-DD')
OR END_DATE IS NULL)
ORDER BY START_DATE
/
GRADE_ID PERIOD_DAY START_DAT END_DATE
---------- ---------- --------- ---------
2 10.9 2000-01-01 2001-02-10
2 9 2001-02-11 2001-02-21
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 2000-01-01 2001-02-10 2001-02-10 2001-02-21+PERIOD VAC_PROPERTY
+ ** ** +
+ |...........||>>>>>>>>>>>>>>>>>>>>>>>>>>||>>>>>>>>>>>>>>>>>>>>|| +
+ +
+ 2000-11-04 2001-02-10 2001-02-10 2001-02-21+PERIOBETWEEN(
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
start_period_dt 2000-11-04 and end_period_dt = 2001-02-21)
now, ** I want compare filter (start_period_dt 2000-11-04 and end_period_dt = 2001-02-21 ) with
START_DAT and END_DATE in the result
except result after compare
GRADE_ID PERIOD_DAY START_DAT END_DATE DIFFERENCE_DAYS
---------- ---------- --------- ----------- ---------------
2 10.9 2000-11-04 2001-02-10 98
2 9 2001-02-11 2001-02-21 10
* END_DATE 2001-02-21 BECAUSE end_period_dt = 2001-02-21
* And now I have two record become
GRADE_ID PERIOD_DAY START_DAT END_DATE DIFFERENCE_DAYS
---------- ---------- --------- ----------- ---------------
2 10.9 2000-11-04 2001-02-10 98
2 9 2001-02-11 2001-02-21 10
( 98 + 10 ) = 107.99 days
recalculate end_period_dt whith new 108 days
end_period_dt = 2000-11-04 + 108 days
*end_period_dt = 2001-02-20
fourth step :
check any period in table emp_transaction match between start_period_date = 2000-11-04 and end_period_dt = 2001-02-20
I take sum days for this period and add to end_date_period
SELECT *
FROM EMP_TRANSACTION
WHERE EMP_ID = 1
AND START_DT <= to_date('2000-11-04','YYYY-MM-DD')
AND END_DT >= to_date('2001-02-20','YYYY-MM-DD')
/
no rows selected
keep same end_period_dt = 2001-02-20
Fifth step :
check any period in table emp_vac where vac_code = 2 match between start_period_date = 2000-11-04
and end_period_dt = 2001-02-20
SELECT *
FROM EMP_VAC
WHERE EMP_ID = 1
AND VAC_CODE = 2
AND START_DT <= to_date('2000-09-26','YYYY-MM-DD')
AND END_DT >= to_date('2000/01/01','YYYY-MM-DD')
/
no rows selected
keep same end_period_dt = 2001-02-20
When execute :
Insert into EMP_VAC(EMP_ID, VAC_LENGTH, START_DT, END_DT, START_PERIOD_DT,END_PERIOD_DT, VAC_CODE)
Values
(1, 10, TO_DATE('2002-05-01', 'YYYY-MM-DD'), TO_DATE('2002-05-10', 'YYYY-MM-DD'), NULL,
NULL, 1);
I WANT TO:
pass to_date('2000-11-04','YYYY-MM-DD') to column START_PERIOD_DT, and
pass to_date('2001-02-20','YYYY-MM-DD') to column END_PERIOD_DT
EXCEPT OUTPUT :
SELECT * FROM EMP_VAC WHERE EMP_ID = 1 AND VAC_CODE = 1
/
EMP_ID VAC_LENGTH START_DT END_DT START_PERIOD_DT END_PERIO VAC_CODE
---------- ---------- --------- --------- --------------- --------- ----------
1 25 2001-02-01 2001-02-25 2000-01-01 2000-11-03 1
1 10 2002-05-01 2002-05-10 2000-11-04 2001-02-20 1
when update column VAC_LENGTH OR START_DT OR END_DT update new values to column START_PERIOD_DT and END_PERIOD_DT,Then
Take same the steps above.
UPDATE EMP_VAC
SET VAC_LENGTH = 12 , END_DT = TO_DATE('2002-05-12', 'YYYY-MM-DD')
WHERE EMP_ID = 1
AND START_DT = TO_DATE('2002-05-01', 'YYYY-MM-DD')
EXCEPT OUTPUT :
SELECT * FROM EMP_VAC WHERE EMP_ID = 1 AND VAC_CODE = 1 AND START_DT = TO_DATE('2002-05-01', 'YYYY-MM-DD')
/
EMP_ID VAC_LENGTH START_DT END_DT START_PERIOD_DT END_PERIO VAC_CODE
---------- ---------- --------- --------- --------------- --------- ----------
1 25 2001-02-01 2001-02-25 2000-01-01 2000-11-03 1
1 12 2002-05-01 2002-05-10 2000-11-04 2001-02-22 1
NOTE : WHEN EMP_ID = 1 TAKE NEXT VACTION AND VAC_CODE = 1 ,
WILL BE START_PEROD_DT = make next day for end_period_dt for prevoius vaction
= 2001-02-22 + 1
START_PEROD_DT = 2001-02-23
I want the relationship between the tables dynamically, to return START_PERIOD_DT,END_PERIOD_DT ,
when insert or update a vacation
Thanks in advance
[Updated on: Wed, 30 November 2011 18:32] Report message to a moderator |
|
|
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #533676 is a reply to message #533613] |
Thu, 01 December 2011 02:03 |
|
orgdn
Messages: 34
Registered: October 2011
|
Member |
|
|
Thanks BlackSwan
Weekend Days calculated within vacation days (vac_length), if the over between start_date and end_date
Holiday days, no calculated within vacation days (vac_length), if the over between start_date and end_date.
Weekend Days and Holiday days, does not affect the calculation of the periods(start_period_dt and end_period_dt)
But in the examples I ignored Holiday days ,when calculate vacation days(vac_length)
[Updated on: Thu, 01 December 2011 02:22] Report message to a moderator |
|
|
|
| Re: error ORA-01733- virtual column not allowed here (2 Merged) [message #534180 is a reply to message #533676] |
Sat, 03 December 2011 16:21 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your script still has errors. You can test it yourself to find them. You have used multiple conflicting data sets in your examples and explanation. You need to pick one and stick to it. There are also errors in your examples and explanation, such as:
"2001-02-30"
There isn't any February 30th in any year.
Another error:
"and the employee take third vaction ( start_date 2002-03-01 & end_date 2002-03-02 ) ,
means take 15days vaction(vac_length),"
March 1st to March 2nd is 2 days, not 15 days.
How can you expect anybody to understand your problem with scripts that don't run and mis-matched data and errors in the examples and explanations?
I attempted to work through your mess, fixing your errors, and trying to understand what you meant, and got as far as making it all work for one grade. I have provided that below. Perhaps you can use that as a starting point and modify it for multiple grades. Notice that I have not used any functions or views. You should be able to make your modifications in the same manner and keep it simple. You could also do the same things through a procedure, instead of a trigger, and many might consider that a better practice.
-- create tables and insert initial data:
SCOTT@orcl_11gR2> CREATE TABLE EMP_HISTORY
2 (
3 EMP_ID NUMBER,
4 HIS_DATE DATE,
5 GRADE NUMBER(1)
6 )
7 /
Table created.
SCOTT@orcl_11gR2> Insert into EMP_HISTORY
2 (EMP_ID, HIS_DATE, GRADE)
3 Values
4 (1, TO_DATE('2000-01-01', 'YYYY-MM-DD'), 1);
1 row created.
SCOTT@orcl_11gR2> --Insert into EMP_HISTORY
SCOTT@orcl_11gR2> -- (EMP_ID, HIS_DATE, GRADE)
SCOTT@orcl_11gR2> -- Values
SCOTT@orcl_11gR2> -- (1, TO_DATE('2000-05-09', 'YYYY-MM-DD'), 2);
SCOTT@orcl_11gR2> CREATE TABLE VAC_PROPERTY
2 (
3 GRADE_ID NUMBER NOT NULL,
4 START_DATE DATE NOT NULL,
5 END_DATE DATE,
6 PERIOD_DAY NUMBER
7
8 )
9 /
Table created.
SCOTT@orcl_11gR2> Insert into VAC_PROPERTY
2 (GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
3 Values
4 (1, TO_DATE('2000-03-21', 'YYYY-MM-DD'), NULL, 9.729);
1 row created.
SCOTT@orcl_11gR2> Insert into VAC_PROPERTY
2 (GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
3 Values
4 (2, TO_DATE('2000-01-01', 'YYYY-MM-DD'), TO_DATE('2001-02-10', 'YYYY-MM-DD'), 10.9);
1 row created.
SCOTT@orcl_11gR2> Insert into VAC_PROPERTY
2 (GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
3 Values
4 (2, TO_DATE('2001-02-11', 'YYYY-MM-DD'), NULL, 9);
1 row created.
SCOTT@orcl_11gR2> Insert into VAC_PROPERTY
2 (GRADE_ID, START_DATE, END_DATE, PERIOD_DAY)
3 Values
4 (1, TO_DATE('1998-01-25', 'YYYY-MM-DD'), TO_DATE('2000-03-20', 'YYYY-MM-DD'), 11.25);
1 row created.
SCOTT@orcl_11gR2> CREATE TABLE EMP_VAC
2 (
3 EMP_ID NUMBER,
4 VAC_LENGTH NUMBER,
5 START_DT DATE,
6 END_DT DATE,
7 START_PERIOD_DT DATE,
8 END_PERIOD_DT DATE,
9 VAC_CODE NUMBER(1)
10 )
11 /
Table created.
-- trigger:
SCOTT@orcl_11gR2> create or replace trigger emp_vac_bir
2 before insert on emp_vac
3 for each row
4 declare
5 v_period_day number;
6 begin
7 :new.vac_length := :new.end_dt - :new.start_dt + 1;
8 select max (end_period_dt)
9 into :new.start_period_dt
10 from emp_vac
11 where rowid != :new.rowid
12 and emp_id = :new.emp_id;
13 if :new.start_period_dt is null then
14 select min (his_date)
15 into :new.start_period_dt
16 from emp_history
17 where emp_id = :new.emp_id;
18 else
19 :new.start_period_dt := :new.start_period_dt + 1;
20 end if;
21 select vp.period_day
22 into v_period_day
23 from emp_history eh, vac_property vp
24 where eh.emp_id = :new.emp_id
25 and eh.grade = vp.grade_id
26 and vp.start_date <= :new.start_period_dt
27 and nvl (vp.end_date, sysdate) >= :new.start_period_dt;
28 :new.end_period_dt := :new.start_period_dt + (:new.vac_length * v_period_day);
29 end emp_vac_bir;
30 /
Trigger created.
SCOTT@orcl_11gR2> show errors
No errors.
-- test:
SCOTT@orcl_11gR2> insert into emp_vac
2 (emp_id, start_dt, end_dt, vac_code)
3 values
4 (1, to_date ('2001-02-01', 'YYYY-MM-DD'), to_date ('2001-02-25', 'YYYY-MM-DD'), 1)
5 /
1 row created.
SCOTT@orcl_11gR2> select * from emp_vac
2 /
EMP_ID VAC_LENGTH START_DT END_DT START_PERI END_PERIOD VAC_CODE
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 25 2001-02-01 2001-02-25 2000-01-01 2000-10-08 1
1 row selected.
SCOTT@orcl_11gR2> -- clean-up of test tables:
SCOTT@orcl_11gR2> drop table emp_vac
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table vac_property
2 /
Table dropped.
SCOTT@orcl_11gR2> drop table emp_history
2 /
Table dropped.
[Updated on: Sat, 03 December 2011 16:22] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Apr 25 21:48:08 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
