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!

Need to alter Update Query

Vemula MuniAug 26 2019 — edited Sep 20 2019

Below is my ps_cts_iqn_empl_wo table structure and data.

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	510	15-Aug-19	28-Aug-19	15	18	43221 Updated start_dt and end_dt column values in below row
570717	12451	210	10-AUG-19	13-AUG-19	15	18	43221
570717	12450	510	15-Aug-19	28-Aug-19	15	18	43221 Updated start_dt and end_dt column values in below row
570717	12450	510	15-JUL-19	28-JUL-19	15	18	43221
570718	12451	510	15-Aug-19	28-Aug-19	15	18	43221

and below is my PS_CT_IQN_ACC_STG table structure and data.

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
570717	510	14-Aug-19

I need to update my PS_CT_IQN_ACC_STG table columns (Work_order_Id,End_Dt, Standard_Rate,CTS_WO_OT_RATE, Vendor_id) as below.

Employee id and Project_id should match in both the tables. and report_due_date should be between start_dt and end_dt in ps_cts_iqn_empl_wo.

If report_due_date not falls between start_dt and end_dt in ps_cts_iqn_empl_wo table then we nee to check for EMPLID and project_id combination match.

if there is more than one row matched in ps_cts_iqn_empl_wo then we need to fetch maximum Work_order_Id row.

first check is to find an maximum active work order related data(employee id and project_id should match in both tables and report_due_date value between start_dt and end_dt in ps_cts_iqn_empl_wo ). If there is no row, we should find any previous max work_order_id value for the emplid and project id combination (even report_due_date value not falls between start_dt and end_dt in ps_cts_iqn_empl_wo).

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
570717	510	14-Aug-19	12451	13-Aug-19	15	18	43221

Create and insert scripts:

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 ('570717','510',to_date('14-AUG-2019','DD-MON-YYYY'),null,null,null,null,null);

SQL to modify :

merge INTO PS_CT_IQN_ACC_STG STG USING ps_cts_iqn_empl_wo WO ON (WO.EMPLID = STG.EMPLID

AND WO.project_id = STG.project_id

AND stg.report_due_date BETWEEN WO.start_dt AND WO.end_dt) WHEN matched THEN

UPDATE

SET STG.WORK_ORDER_ID = WO.WORK_ORDER_ID , STG.END_DT = WO.END_DT , STG.STANDARD_RATE = WO.STANDARD_RATE ,STG.CTS_WO_OT_RATE = WO.CTS_WO_OT_RATE, STG.VENDOR_ID = WO.VENDOR_ID

WHERE WO.WORK_ORDER_ID = (

SELECT MAX(WORK_ORDER_ID)

FROM ps_cts_iqn_empl_wo WO1

WHERE WO1.EMPLID = STG.EMPLID

AND WO1.project_id = STG.project_id

AND stg.report_due_date BETWEEN WO1.start_dt AND WO1.END_DT )

Message was edited by: Vemula Muni

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

Jump to Answer

Jeevanantham V

Employee id and Project_id should match in both the tables. and report_due_date should be between start_dt and end_dt in ps_cts_iqn_empl_wo.

If report_due_date not falls between start_dt and end_dt in ps_cts_iqn_empl_wo table then we nee to check for EMPLID and project_id combination match.

Few open points before proceed further:

If report date not falls between start data and end date, EMP id and Project id should be considered. Suppose report falls between start and end date and having two entries means, how to proceed further?

If your answer is "max product id", then i feel, there is no logic in validating report date, because , both scenario will come under Max product id

Jeevanantham V

EMPL_ID: 570718 is mapped to Project_id: 530 in table:ps_cts_iqn_empl_wo, but in source table, it is mapped to 510. Can you provide the valid input details?

Vemula Muni

Hi @"AJ." Below is inputs to your quires.

Few open points before proceed further:

EMP id and Project id matched and report date falls between start data and end date, and having more than one entries , how to proceed further? Need to update MAX(WORK_ORDER_ID ) row data from ps_cts_iqn_empl_wo

If report date not falls between start data and end date, EMP id and Project id matched. if having more than one entry then ,Need to update MAX(WORK_ORDER_ID ) row data from ps_cts_iqn_empl_wo

EMPL_ID: 570718 is mapped to Project_id: 530 in table:ps_cts_iqn_empl_wo, but in source table, it is mapped to 510. Can you provide the valid input details? typo error employee id is 570716.

Anton Scheffer

update PS_CT_IQN_ACC_STG stg

set ( stg.work_order_id, stg.end_dt, stg.standard_rate, stg.cts_wo_ot_rate, stg.vendor_id ) =

( select max( wo.work_order_id ) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) work_order_id

, max( wo.end_dt ) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) end_dt

, max( wo.standard_rate) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) standard_rate

, max( wo.cts_wo_ot_rate) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id ) cts_wo_ot_rate

, max( wo.vendor_id ) keep ( dense_rank last order by case when stg.report_due_date between wo.start_dt and wo.end_dt then 1 end, wo.work_order_id )

from ps_cts_iqn_empl_wo wo

where wo.emplid = stg.emplid

and wo.project_id = stg.project_id

)

report error:
Oops, something went wrong!  We've notified our team to take a look. Please try again later. (Reference 300000.25.203.84785).

Added on Aug 26 2019

#sql-pl-sql

13 comments

402 views

SQL & PL/SQL

Need to alter Update Query

Comments

Post Details