Muneendra
Reputation: 25
Update Statement With DENSE_RANK
DB Version : Oracle 12C
Below is my data.
PROCESS_INSTANCE ,ACCRUAL_YEAR, ACCR_PERIOD, ACCRUAL_YEAR1, ACCR_PERIOD1,PROJECT_ID,EMPLID, PERIOD_END_DT, REPORT_DUE_DATE are key fields in my table.
I want to update REGULAR_HOURS, CTS_ACC_OT_HRS Column value as STANDARD_HOURS in same table.
Whenever employee has more than one row for the Key field combination (excluding PROJECT_ID key), then I want to update REGULAR_HOURS, CTS_ACC_OT_HRS in fist row by using Rank (process_instance,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,EMPLID,PERIOD_END_DT,REPORT_DUE_DATE).
PROCESS_INSTANCE ACCRUAL_YEAR ACCR_PERIOD ACCRUAL_YEAR1 ACCR_PERIOD1 PROJECT_ID EMPLID PERIOD_END_DT REPORT_DUE_DATE STANDARD_HOURS REGULAR_HOURS OVERTIME_HOURS CTS_ACC_OT_HRS
22196108 2019 8 2019 5 1000237975 787792 03-MAY-19 01-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 01-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 02-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 03-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 06-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 07-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 08-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 09-MAY-19 9 0 0 0
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 10-MAY-19 9 0 0 0
Required output:
In below data employee 787792 has two rows for the REPORT_DUE_DATE 01-MAY-19.
So I want to update REGULAR_HOURS and CTS_ACC_OT_HRS as STANDARD_HOURS for one row for report due date 01-MAY-19.
Note: need to update STANDARD_HOURS as 0 for the extra rows.
PROCESS_INSTANCE ACCRUAL_YEAR ACCR_PERIOD ACCRUAL_YEAR1 ACCR_PERIOD1 PROJECT_ID EMPLID PERIOD_END_DT REPORT_DUE_DATE STANDARD_HOURS REGULAR_HOURS OVERTIME_HOURS CTS_ACC_OT_HRS
22196108 2019 8 2019 5 1000237975 787792 03-MAY-19 01-MAY-19 9 9 0 9
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 01-MAY-19 0 0 0 0
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 02-MAY-19 9 9 0 9
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 03-MAY-19 9 9 0 9
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 06-MAY-19 9 9 0 9
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 07-MAY-19 9 9 0 9
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 08-MAY-19 9 9 0 9
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 09-MAY-19 9 9 0 9
22196108 2019 8 2019 5 1000251958 787792 10-MAY-19 10-MAY-19 9 9 0 9
CREATE TABLE PS_CT_IQN_ACC_STG (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
ACCRUAL_YEAR SMALLINT NOT NULL,
ACCR_PERIOD SMALLINT NOT NULL,
ACCRUAL_YEAR1 SMALLINT NOT NULL,
ACCR_PERIOD1 SMALLINT NOT NULL,
CTS_ACCRUAL_TYPE VARCHAR2(3) NULL,
CTS_BLNK_TS_FLAG VARCHAR2(1) NULL,
PROJECT_ID VARCHAR2(15) NOT NULL,
ALLOCATION_PERCENT DECIMAL(13, 10) NULL,
PROJ_ACTIVITY_ID VARCHAR2(15) NULL,
EMPLID VARCHAR2(11) NOT NULL,
HIRE_DT DATE,
ACTION_DT DATE,
ACTION VARCHAR2(3) NULL,
CTS_WO_ID VARCHAR2(15) NULL,
CTS_WO_END_DATE DATE,
CTS_WO_REG_RATE DECIMAL(15, 2) NULL,
CTS_WO_OT_RATE DECIMAL(15, 2) NULL,
CTS_WO_CURRENCY VARCHAR2(3) NULL,
STANDARD_RATE DECIMAL(15, 2) NULL,
CURRENCY_CD VARCHAR2(3) NULL,
VENDOR_ID VARCHAR2(10) NULL,
CTS_OT_BASIS VARCHAR2(10) NULL,
COUNTRY VARCHAR2(3) NULL,
LOCATION VARCHAR2(10) NULL,
HOLIDAY_SCHEDULE VARCHAR2(6) NULL,
BUSINESS_UNIT VARCHAR2(5) NULL,
PERIOD_END_DT DATE,
REPORT_DUE_DATE DATE,
CTS_WORKDAY VARCHAR2(3) NULL,
TIME_QUANTITY DECIMAL(14, 2) NULL,
STANDARD_HOURS DECIMAL(14, 2) NULL,
REGULAR_HOURS DECIMAL(15, 3) NULL,
OVERTIME_HOURS DECIMAL(14, 2) NULL,
TIME_SHEET_STATUS VARCHAR2(3) NULL,
POLICY_ID VARCHAR2(50) NULL,
CTS_ACC_REG_HRS DECIMAL(5, 2) NULL,
CTS_ACC_OT_HRS DECIMAL(5, 2) NULL,
CTS_ACCR_BASE_AMT DECIMAL(15, 2) NULL,
CTS_ACCR_OT_AMT DECIMAL(15, 2) NULL,
CTS_ACCR_AMT_TOTAL DECIMAL(15, 2) NULL);
CREATE UNIQUE iNDEX PS_CT_IQN_ACC_STG ON PS_CT_IQN_ACC_STG
(PROCESS_INSTANCE,
ACCRUAL_YEAR,
ACCR_PERIOD,
ACCRUAL_YEAR1,
ACCR_PERIOD1,
PROJECT_ID,
EMPLID,
PERIOD_END_DT,
REPORT_DUE_DATE);
Insert Scripts:
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000237975',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('03-MAY-2019','DD-MON-YYYY'),to_date('01-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('03-MAY-2019','DD-MON-YYYY'),to_date('01-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('03-MAY-2019','DD-MON-YYYY'),to_date('02-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('03-MAY-2019','DD-MON-YYYY'),to_date('03-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('10-MAY-2019','DD-MON-YYYY'),to_date('06-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('10-MAY-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('10-MAY-2019','DD-MON-YYYY'),to_date('08-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('10-MAY-2019','DD-MON-YYYY'),to_date('09-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);
Insert into PS_CT_IQN_ACC_STG (PROCESS_INSTANCE,ACCRUAL_YEAR,ACCR_PERIOD,ACCRUAL_YEAR1,ACCR_PERIOD1,CTS_ACCRUAL_TYPE,CTS_BLNK_TS_FLAG,PROJECT_ID,ALLOCATION_PERCENT,PROJ_ACTIVITY_ID,EMPLID,HIRE_DT,ACTION_DT,ACTION,CTS_WO_ID,CTS_WO_END_DATE,CTS_WO_REG_RATE,CTS_WO_OT_RATE,CTS_WO_CURRENCY,STANDARD_RATE,CURRENCY_CD,VENDOR_ID,CTS_OT_BASIS,COUNTRY,LOCATION,HOLIDAY_SCHEDULE,BUSINESS_UNIT,PERIOD_END_DT,REPORT_DUE_DATE,CTS_WORKDAY,TIME_QUANTITY,STANDARD_HOURS,REGULAR_HOURS,OVERTIME_HOURS,TIME_SHEET_STATUS,POLICY_ID,CTS_ACC_REG_HRS,CTS_ACC_OT_HRS,CTS_ACCR_BASE_AMT,CTS_ACCR_OT_AMT,CTS_ACCR_AMT_TOTAL) values (22196108,2019,8,2019,5,'H','Y','1000251958',0,'TBD','787792',to_date('29-APR-2019','DD-MON-YYYY'),to_date('07-MAY-2019','DD-MON-YYYY'),'XFR','12697051',to_date('28-OCT-2019','DD-MON-YYYY'),110,110,' ',65,'USD','0000010018','Weekly','USA','USGADUUA01','HUSA','US410',to_date('10-MAY-`2019','DD-MON-YYYY'),to_date('10-MAY-2019','DD-MON-YYYY'),'Y',0,9,0,0,' ','Project',0,0,0,0,0);`
Upvotes: 1
Views: 263
Answers (1)
ArtBajji
Reputation: 960
The question is not saying how to sort between the two rows sharing the same REPORT_DUE_DATE. So PROJECT_ID is used in the ORDER BY clause. Please change this according to your requirement of sorting.
merge into PS_CT_IQN_ACC_STG a
using (
select PROCESS_INSTANCE,
ACCRUAL_YEAR,
ACCR_PERIOD,
ACCRUAL_YEAR1,
ACCR_PERIOD1,
PROJECT_ID,
EMPLID,
PERIOD_END_DT,
REPORT_DUE_DATE,
case when rn = 1
then standard_hours
else regular_hours end regular_hours,
case when rn = 1
then standard_hours
else cts_Acc_ot_hrs end cts_Acc_ot_hrs,
case when rn = 1
then standard_hours
else 0 end standard_hours,rn
from (
select PROCESS_INSTANCE,
ACCRUAL_YEAR,
ACCR_PERIOD,
ACCRUAL_YEAR1,
ACCR_PERIOD1,
PROJECT_ID,
EMPLID,
PERIOD_END_DT,
REPORT_DUE_DATE,
regular_hours,
standard_hours,
cts_Acc_ot_hrs,
row_number() over( partition by PROCESS_INSTANCE,
ACCRUAL_YEAR,
ACCR_PERIOD,
ACCRUAL_YEAR1,
ACCR_PERIOD1,
--PROJECT_ID,
EMPLID,
PERIOD_END_DT,
report_due_date
order by PROJECT_ID) rn
from PS_CT_IQN_ACC_STG)
) b
on ( a.PROCESS_INSTANCE = b.PROCESS_INSTANCE
and a.ACCRUAL_YEAR = b.ACCRUAL_YEAR
and a.ACCR_PERIOD = b.ACCR_PERIOD
and a.ACCRUAL_YEAR1 = b.ACCRUAL_YEAR1
and a.ACCR_PERIOD1 = b.ACCR_PERIOD1
and a.PROJECT_ID = b.PROJECT_ID
and a.EMPLID = b.EMPLID
and a.PERIOD_END_DT = b.REPORT_DUE_DATE)
when matched
then update set a.REGULAR_HOURS = b.REGULAR_HOURS,
a.CTS_ACC_OT_HRS = b.CTS_ACC_OT_HRS,
a.STANDARD_HOURS = b.STANDARD_HOURS;
I tried this in Oracle Live SQL.
The query in Using Clause runs fine and produces the expected output.
PROCESS_INSTANCE ACCRUAL_YEAR ACCR_PERIOD ACCRUAL_YEAR1 ACCR_PERIOD1 PROJECT_ID EMPLID PERIOD_END_DT REPORT_DUE_DATE REGULAR_HOURS CTS_ACC_OT_HRS STANDARD_HOURS RN
22196108 2019 8 2019 5 1000237975 787792 03-MAY-19 01-MAY-19 9 9 9 1
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 01-MAY-19 0 0 0 2
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 02-MAY-19 9 9 9 1
But the merge statement is not working as expected. The second row is getting all 9s. The first row retains 9 for standard_hours instead of updating it to zero.
PROCESS_INSTANCE ACCRUAL_YEAR ACCR_PERIOD ACCRUAL_YEAR1 ACCR_PERIOD1 PROJECT_ID EMPLID PERIOD_END_DT REPORT_DUE_DATE REGULAR_HOURS CTS_ACC_OT_HRS STANDARD_HOURS
22196108 2019 8 2019 5 1000237975 787792 03-MAY-19 01-MAY-19 0 0 9
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 01-MAY-19 9 9 9
22196108 2019 8 2019 5 1000251958 787792 03-MAY-19 02-MAY-19 9 9 9
I created a smaller version of your table with SMALLINT, DECIMAL, DATE and VARCHAR2 datatype columns in an Oracle 12c database. The MERGE statement works fine on Oracle 12c database.
I am not sure why this does not work in Oracle Live SQL. But the solution works on a miniature model table on Oracle 12c Database. Please check the solution on your database. Hope it works.
Upvotes: 1
Related Questions
- How to update table using rank function
- How do I update a value based on the dense_rank result?
- MySQL update same table field from DENSE_RANK() results
- Run dense_rank() window function in update query - Oracle
- SQL update in ranking order
- DENSE_RANK () trick Oracle
- dense_RANK() function, how can I fix? oracle
- Update Postgresql table using rank()
- MS Access: use dense_rank in update
- Oracle SQL - DENSE_RANK