0

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.

Zoe
  • 27,060
  • 21
  • 118
  • 148
KISHORE
  • 1
  • 1
  • Your subselect for CLASS_FLD is not returning a value so it gets set to null. What should CLASS_FLD be set to if the subselect does not return a value? or is this a data problem and the subselect should always return a value? If it is valid that the subselect won't return a value, then you need to wrap the subselect in a coalesce(). If it's not valid, either you have a data issue or you need to add a where clause to your update to limit which rows are updated. – Darryls99 Jan 13 '23 at 20:50

1 Answers1

0

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
    ...
otherted
  • 151
  • 3