This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Categories

ora-01407 cannot update to null

Vemula Muni
Vemula Muni Member Posts: 57
edited Sep 20, 2019 12:01PM in SQL & PL/SQL

Source table Data : ps_cts_iqn_empl_wo

EMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID
5707161245653011-Aug-1931-Aug-19101545621
5707161245253011-Aug-1931-Aug-19141545621
5707171245121010-AUG-1913-AUG-19151843221
5707171245051015-JUL-1928-JUL-19151843221
5707181245151015-Aug-1928-Aug-19151843221

Target Table :

EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id
57071653014-Aug-19
57071653013-Aug-19
57071751014-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.

EMPLIDProject_Idreport_due_dateWork_order_IdEnd_DtStandard_RateCTS_WO_OT_RATE            Vendor_id
57071653014-Aug-191245631-Aug-19101545621
57071653013-Aug-191245631-Aug-19101545621
57071751014-Aug-191245113-Aug-19151843221

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

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 26, 2019 12:57PM Answer ✓

    Hi,

    Vemula Muni wrote:Source table Data : ps_cts_iqn_empl_woEMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID5707161245653011-Aug-1931-Aug-191015456215707161245253011-Aug-1931-Aug-191415456215707171245121010-AUG-1913-AUG-191518432215707171245051015-JUL-1928-JUL-191518432215707181245151015-Aug-1928-Aug-19151843221Target Table :EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id57071653014-Aug-1957071751014-Aug-19Required Output :EMPLIDProject_Idreport_due_dateWork_order_IdEnd_DtStandard_RateCTS_WO_OT_RATE Vendor_id57071653014-Aug-191245631-Aug-1910154562157071751014-Aug-191245113-Aug-19151843221
    1. CREATETABLEps_cts_iqn_empl_wo(EMPLIDVARCHAR2(11)NOTNULL,
    2. WORK_ORDER_IDVARCHAR2(15)NOTNULL,
    3. PROJECT_IDVARCHAR2(15)NOTNULL,
    4. START_DTDATE,
    5. END_DTDATE,
    6. STANDARD_RATEDECIMAL(15,2)NULL,
    7. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    8. VENDOR_IDVARCHAR2(10)NULL);
    11. Insertintops_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');
    12. Insertintops_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');
    13. Insertintops_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');
    14. Insertintops_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');
    15. Insertintops_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');
    19. CREATETABLEPS_CT_IQN_ACC_STG(EMPLIDVARCHAR2(11)NOTNULL,
    20. PROJECT_IDVARCHAR2(15)NOTNULL,
    21. REPORT_DUE_DATEDATE,
    22. WORK_ORDER_IDVARCHAR2(15)NULL,
    23. END_DTDATE,
    24. STANDARD_RATEDECIMAL(15,2)NULL,
    25. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    26. VENDOR_IDVARCHAR2(10)NULL)
    31. InsertintoPS_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);
    32. InsertintoPS_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);
    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); 
    QueryUPDATE PS_CT_IQN_ACC_STG stgSET (Work_order_Id,WO_End_Dt,WO_Standard_Rate,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 WO1where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLIDAND WO.project_id = STG.project_id and stg.report_due_date between WO.start_dt and WO.end_dt ))

    Thanks for posting the CREATE TABLE and INSERT statements.  Don't forget to post your Oracle version.

    See the Forum FAQ:

    When I run the UPDATE statement you posted with the sample data you posted, I get this error:

      SET (Work_order_Id,WO_End_Dt,WO_Standard_Rate,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                                                 *ERROR at line 2:ORA-00904: "WO_OT_RATE": invalid identifier

    There is a column called CTS_WO_OT_RATE, but not WO_OT_RATE.

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 26, 2019 12:57PM Answer ✓

    Hi,

    Vemula Muni wrote:Source table Data : ps_cts_iqn_empl_woEMPLIDWork_order_IdProject_IdStart_DtEnd_DtStandard_RateCTS_WO_OT_RATEVendor_ID5707161245653011-Aug-1931-Aug-191015456215707161245253011-Aug-1931-Aug-191415456215707171245121010-AUG-1913-AUG-191518432215707171245051015-JUL-1928-JUL-191518432215707181245151015-Aug-1928-Aug-19151843221Target Table :EMPLIDProject_Idreport_due_dateWork_order_IdWO_End_DtWO_Standard_RateWO_OT_RateVendor_id57071653014-Aug-1957071751014-Aug-19Required Output :EMPLIDProject_Idreport_due_dateWork_order_IdEnd_DtStandard_RateCTS_WO_OT_RATE Vendor_id57071653014-Aug-191245631-Aug-1910154562157071751014-Aug-191245113-Aug-19151843221
    1. CREATETABLEps_cts_iqn_empl_wo(EMPLIDVARCHAR2(11)NOTNULL,
    2. WORK_ORDER_IDVARCHAR2(15)NOTNULL,
    3. PROJECT_IDVARCHAR2(15)NOTNULL,
    4. START_DTDATE,
    5. END_DTDATE,
    6. STANDARD_RATEDECIMAL(15,2)NULL,
    7. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    8. VENDOR_IDVARCHAR2(10)NULL);
    11. Insertintops_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');
    12. Insertintops_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');
    13. Insertintops_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');
    14. Insertintops_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');
    15. Insertintops_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');
    19. CREATETABLEPS_CT_IQN_ACC_STG(EMPLIDVARCHAR2(11)NOTNULL,
    20. PROJECT_IDVARCHAR2(15)NOTNULL,
    21. REPORT_DUE_DATEDATE,
    22. WORK_ORDER_IDVARCHAR2(15)NULL,
    23. END_DTDATE,
    24. STANDARD_RATEDECIMAL(15,2)NULL,
    25. CTS_WO_OT_RATEDECIMAL(15,2)NULL,
    26. VENDOR_IDVARCHAR2(10)NULL)
    31. InsertintoPS_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);
    32. InsertintoPS_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);
    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); 
    QueryUPDATE PS_CT_IQN_ACC_STG stgSET (Work_order_Id,WO_End_Dt,WO_Standard_Rate,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 WO1where WO1.Work_order_Id= (select max(Work_order_Id) from ps_cts_iqn_empl_wo WO where WO.EMPLID = STG.EMPLIDAND WO.project_id = STG.project_id and stg.report_due_date between WO.start_dt and WO.end_dt ))

    Thanks for posting the CREATE TABLE and INSERT statements.  Don't forget to post your Oracle version.

    See the Forum FAQ:

    When I run the UPDATE statement you posted with the sample data you posted, I get this error:

      SET (Work_order_Id,WO_End_Dt,WO_Standard_Rate,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                                                 *ERROR at line 2:ORA-00904: "WO_OT_RATE": invalid identifier

    There is a column called CTS_WO_OT_RATE, but not WO_OT_RATE.

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 26, 2019 1:22PM

    Hi @Frank Kulash, please find the modified query.

    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  ))

    I hav

    · Share on TwitterShare on Facebook
  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Aug 26, 2019 1:36PM

    I've just run the script you posted, adding a missing ";" to the second create table, and got no errors:

    pastedImage_0.png

    Can you show us where do you get the ORA-01407 error??

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 26, 2019 1:45PM

    Hi @L. Fernigrini,  when i am trying to execute below query I am getting that error.

    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  ))

    I hav

    · Share on TwitterShare on Facebook
  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Aug 26, 2019 1:55PM

    that's strange, with the sample data you posted, I'm not getting that error:

    pastedImage_0.png

    Can you show the entire script and it's output? Please post the entire error message.

    Can you tell us full Oracle version (e.g., 11.2.0.4).

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 26, 2019 3:23PM

    Hi,

    I don't get any error, either.  I suspect you're not running the statements you posted.

    What is the difference between this problem and the one you posted earlier today:

    Need to alter Update Query

    ?  The solutions from that problem do what you requested in this problem, too.

    · Share on TwitterShare on Facebook
  • Vemula Muni
    Vemula Muni Member Posts: 57
    edited Aug 26, 2019 11:40PM

    Hi @L. Fernigrini,  Could you please insert the below row to replicate the issue.

    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);

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 27, 2019 6:13AM

    Hi,

    Vemula Muni wrote:Hi L. Fernigrini, Could you please insert the below row to replicate the issue.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);

    Is the new row in addition to the row that already has emplid='5070716', or instead of it?

    Either way, I don't get any error when I run the UPDATE statement in reply #4. 

    ORA-01407 occurs when you try to set a NOT NULL column to NULL.  The UPDATE statement you posted in reply #4 does not set any NOT NULL columns.  If you're getting ORA-01407, then either your table or your UPDATE statement is different from what you said.

    Vemula Muni
    · Share on TwitterShare on Facebook
  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Aug 27, 2019 8:37AM

    Still cannot reproduce the error.

    Can you post the entire script that gives you error, and the output? And also include the database version. It "may" be some strange bug if you get that error message with the script you provided us.

    · Share on TwitterShare on Facebook