- ORA-01407: cannot update ("PSOWNER"."PS_VCHR_LINE_STG"."CLASS_FLD") to NULL Failed SQL stmt: UPDATE

Question

Error Message:- ORA-01407: cannot update ("PSOWNER"."PS_VCHR_LINE_STG"."CLASS_FLD") to NULL Failed SQL stmt: UPDATE

When I am generating the report it is saying NO Success in Peoplesoft.

Below is the code for the Update statement.

Please help me how to overcome this problem.

UPDATE %Table(VCHR_LINE_STG) A 
  SET A.CLASS_FLD = ( 
 SELECT SUBSTR(DCP_FLD49 
 ,3 
 ,4) 
  FROM %Table(DCP_AP11_TMP2) 
 WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND DCP_FLD34= A.VOUCHER_LINE_NUM),A.BUSINESS_UNIT =( 
 SELECT D.CF_ATTRIB_VALUE 
  FROM %Table(CF_ATTRIB_TBL) D 
  , %Table(DEPT_TBL) E 
 WHERE ( D.EFFDT = ( 
 SELECT MAX(D_ED.EFFDT) 
  FROM %Table(CF_ATTRIB_TBL) D_ED 
 WHERE D.SETID = D_ED.SETID 
   AND D.CHARTFIELD_VALUE = D_ED.CHARTFIELD_VALUE 
   AND D_ED.EFFDT <= SYSDATE) 
   AND E.EFFDT=D.EFFDT 
   AND D.CHARTFIELD_VALUE = ( 
 SELECT M.DCP_FLD41 
  FROM %Table(DCP_AP11_TMP2) M 
 WHERE M.VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND M.DCP_FLD34= A.VOUCHER_LINE_NUM) 
   AND D.SETID = E.SETID 
   AND D.SETID = 'DCPID' 
   AND D.CF_ATTRIBUTE='AP_BUSN_UNIT' 
   AND E.EFFDT = ( 
 SELECT MAX(E_ED.EFFDT) 
  FROM %Table(DEPT_TBL) E_ED 
 WHERE E.SETID = E_ED.SETID 
   AND E.DEPTID = E_ED.DEPTID 
   AND E_ED.EFFDT <= SYSDATE) 
   AND E.DEPTID = D.CHARTFIELD_VALUE 
   AND E.SETID = D.SETID 
   AND E.EFF_STATUS='A')),A.BUSINESS_UNIT_GL=( 
 SELECT D.CF_ATTRIB_VALUE 
  FROM %Table(CF_ATTRIB_TBL) D 
  , %Table(DEPT_TBL) E 
 WHERE ( D.EFFDT = ( 
 SELECT MAX(D_ED.EFFDT) 
  FROM %Table(CF_ATTRIB_TBL) D_ED 
 WHERE D.SETID = D_ED.SETID 
   AND D.CHARTFIELD_VALUE = D_ED.CHARTFIELD_VALUE 
   AND D_ED.EFFDT <= SYSDATE) 
   AND E.EFFDT=D.EFFDT 
   AND D.CHARTFIELD_VALUE = ( 
 SELECT M.DCP_FLD41 
  FROM %Table(DCP_AP11_TMP2) M 
 WHERE M.VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND M.DCP_FLD34= A.VOUCHER_LINE_NUM) 
   AND D.SETID = E.SETID 
   AND D.SETID = 'DCPID' 
   AND D.CF_ATTRIBUTE='GL_BUSN_UNIT' 
   AND E.EFFDT = ( 
 SELECT MAX(E_ED.EFFDT) 
  FROM %Table(DEPT_TBL) E_ED 
 WHERE E.SETID = E_ED.SETID 
   AND E.DEPTID = E_ED.DEPTID 
   AND E_ED.EFFDT <= SYSDATE) 
   AND E.DEPTID = D.CHARTFIELD_VALUE 
   AND E.SETID = D.SETID  
   AND E.EFF_STATUS='A')) 
 WHERE EXISTS ( 
 SELECT 'X' 
  FROM %Table(DCP_AP11_TMP2) 
 WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM)

Above is the code for the Update statement in App engine.

Please help me how to overcome this problem.

Thanks in Advance.

Answer 1

The sub-selects populating each field are not returning values so the database is trying to update the field to NULL. In PeopleSoft null values are not allowed in character fields. A field with no value needs to be set to a single space, like ' '.

You will need to wrap each sub-select with a COALESCE() function, with a non-null alternative option if the sub-select does not return a value. Character fields need to be set to ' ', numbers to 0 if no values returned. Date fields can be null. Here is an example using the first few lines of the code provided.

    UPDATE %Table(VCHR_LINE_STG) A 
    SET A.CLASS_FLD = 
      COALESCE(
        (SELECT SUBSTR(DCP_FLD49,3,4) 
         FROM %Table(DCP_AP11_TMP2) 
         WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
         AND DCP_FLD34= A.VOUCHER_LINE_NUM), ' ')
    , A.BUSINESS_UNIT_GL=(SELECT
    ...

Answer 2

I would do this: copy-paste the SELECT statement into SQL Developer - and resolve the '%' parts - and change the Select to * so that all fields can be seen.

Then hopefully you will see the bad data causing this issue.

I'm assuming you had this trouble with PS-delivered job. This kind of thing happens to us - where bad data is not trapped by Online PeopleSoft pages - so then the job fails on missing data. Or just something strange happened in an online page - network fail - who knows - and data is missed.

That is my suggestion - try capturing the SELECT statement to find the bad data.

Best wishes!