SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ora-01407 cannot update to null

Vemula MuniAug 26 2019 — edited Sep 20 2019

Source table Data : ps_cts_iqn_empl_wo

EMPLID	Work_order_Id	Project_Id	Start_Dt	End_Dt	Standard_Rate	CTS_WO_OT_RATE	Vendor_ID
570716	12456	530	11-Aug-19	31-Aug-19	10	15	45621
570716	12452	530	11-Aug-19	31-Aug-19	14	15	45621
570717	12451	210	10-AUG-19	13-AUG-19	15	18	43221
570717	12450	510	15-JUL-19	28-JUL-19	15	18	43221
570718	12451	510	15-Aug-19	28-Aug-19	15	18	43221

Target Table :

EMPLID	Project_Id	report_due_date	Work_order_Id	WO_End_Dt	WO_Standard_Rate	WO_OT_Rate	Vendor_id
570716	530	14-Aug-19
570716	530	13-Aug-19
570717	510	14-Aug-19

Required Output :

case 1 :

----------

When employee id and project id matched and report_due_date falls between start_dt and end_dt. and more than one row present for this combination then i need to update max(work_order_id) row values in target.

Case 2 :

-------

When employee id and project id matched and report_due_date not falls between start_dt and end_dt. i need to fetch a maximum work_order_id row and Work_order_Id end_dt should be less than report_due_date.

EMPLID	Project_Id	report_due_date	Work_order_Id	End_Dt	Standard_Rate	CTS_WO_OT_RATE	Vendor_id
570716	530	14-Aug-19	12456	31-Aug-19	10	15	45621
570716	530	13-Aug-19	12456	31-Aug-19	10	15	45621
570717	510	14-Aug-19	12451	13-Aug-19	15	18	43221

CREATE TABLE ps_cts_iqn_empl_wo (EMPLID VARCHAR2(11) NOT NULL,
   WORK_ORDER_ID VARCHAR2(15) NOT NULL,
   PROJECT_ID VARCHAR2(15) NOT NULL,
   START_DT DATE,
   END_DT DATE,
   STANDARD_RATE DECIMAL(15, 2)  NULL,
   CTS_WO_OT_RATE DECIMAL(15, 2)  NULL,
   VENDOR_ID VARCHAR2(10)  NULL) ;
   
   
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12456','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),10,15,'45621');   
  Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','12452','530',to_date('11-AUG-2019','DD-MON-YYYY'),to_date('31-AUG-2019','DD-MON-YYYY'),14,15,'45621');
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12451','510',to_date('10-AUG-2019','DD-MON-YYYY'),to_date('13-AUG-2019','DD-MON-YYYY'),15,18,'43221');  
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','12450','510',to_date('15-JUL-2019','DD-MON-YYYY'),to_date('28-JUL-2019','DD-MON-YYYY'),15,18,'43221');  
 Insert into ps_cts_iqn_empl_wo (EMPLID,WORK_ORDER_ID,PROJECT_ID,START_DT,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570718','12451','510',to_date('15-AUG-2019','DD-MON-YYYY'),to_date('28-AUG-2019','DD-MON-YYYY'),15,18,'43221');  
   
   
CREATE TABLE PS_CT_IQN_ACC_STG (EMPLID VARCHAR2(11) NOT NULL,
   PROJECT_ID VARCHAR2(15) NOT NULL,
   REPORT_DUE_DATE DATE,
   WORK_ORDER_ID VARCHAR2(15)  NULL,
   END_DT DATE,
   STANDARD_RATE DECIMAL(15, 2)  NULL,
   CTS_WO_OT_RATE DECIMAL(15, 2)  NULL,
   VENDOR_ID VARCHAR2(10)  NULL) 
Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','530',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);
Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570716','530',to_date('13-AUG-2019','DD-MON-YYYY'),null,null,null,null,null); 
Insert into PS_CT_IQN_ACC_STG (EMPLID,PROJECT_ID,REPORT_DUE_DATE,WORK_ORDER_ID,END_DT,STANDARD_RATE,CTS_WO_OT_RATE,VENDOR_ID) values ('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);

Query

UPDATE PS_CT_IQN_ACC_STG stg

SET (Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_Rate,Vendor_id) = (select Work_order_Id,End_Dt,Standard_Rate,CTS_WO_OT_RATE,Vendor_ID from ps_cts_iqn_empl_wo WO1

where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLID

AND WO.project_id = STG.project_id and stg.report_due_date between WO.start_dt and WO.end_dt ))

Message was edited by: Vemula Muni

This post has been answered by Frank Kulash on Aug 26 2019

Jump to Answer

Added on Aug 26 2019

#sql-pl-sql

9 comments

1,564 views

SQL & PL/SQL

ora-01407 cannot update to null

Comments

Post Details