Detailed description of my question:
I have a table (CAS_SV_RQ_APVL_HST)which tracks how a task is moving from one user's queue to another.
I have these columns.
SVC_REQS_NBR,
COMP_REP_USER,
ROLE_NAME,
DATE_AVBL,
CRAT_DATE
For every SVC_REQS_NBR i have bunch of users and one task moves from one user to another for that SVC_REQS_NBR.
Of these columns i have CRAT_DATE values which give when a task is completed by that user.I tried to ORDER BY CRAT_DATE and using ROWNUM wanted to update DATE_AVBL for next user to be CRAT_DATE of previous user.
REASON FOR DOING THIS WHICH YOU MIGHT NOT EVEN CARE: I have correct CRAT_DATE for whole table but DATE_AVBL column is all messed up and hence i want this update.
please look at the UPDATE sql i tried and the error i got
1 UPDATE
2 ( SELECT ROWNUM,DATE_AVBL,CRAT_DATE
,SVC_REQS_
NBR FROM CAS_SV_RQ_APVL_HST
3 WHERE ROLE_NAME = 'UP UM REVIEWER'
4 ORDER BY CRAT_DATE
5 ) a
6 set DATE_AVBL = (select b.CRAT_DATE
7 from (SELECT ROWNUM,DATE_AVBL,CRAT_DATE
,SVC_REQS_
NBR FROM CAS_SV_RQ_APVL_HST
8 WHERE ROLE_NAME = 'UP UM REVIEWER'
9 ORDER BY CRAT_DATE
10 ) b
11 where b.ROWNUM = (a.ROWNUM - 1)
12 AND b.SVC_REQS_NBR = a.SVC_REQS_NBR
13* )
SQL> /
where b.ROWNUM = (a.ROWNUM - 1)
*
ERROR at line 11:
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-01747: invalid user.table.column, table.column, or column specification
Column descriptions :
SVC_REQS_NBR NOT NULL NUMBER(10)
CRAT_DATE NOT NULL DATE
COMP_REPR_USER NOT NULL VARCHAR2(7)
ROLE_NAME NOT NULL VARCHAR2(20)
DATE_AVBL DATE
ACTN_DATE DATE
ANY HELP WOULD BE REALLY APPRECIATED.
Start Free Trial
