You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

ORA-01407: Cannot Update ('SYS ADM'.'PS STAGE TMP'.'DEPTID') to NULL

2 replies [Last post]
Guest's picture
Guest

Hi All,

I am facing ORA-01407 error while updating deptid field but when I checked the SELECT query rt there was no such data that holds NULL deptid in fact there were 12 rows with DEPTID data in it.

UPDATE %Table(PS_STAGE_TMP)TMP
SET TMP.DEPTID = (
SELECT BI.DEPTID
FROM PS_BUS_UNIT_BI BI
WHERE BI.BUSINESS_UNIT = TMP.BUSINESS_UNIT_BI
AND BI.PLACEMENT_TYPE=TMP.PLACEMENT_TYPE
AND BI.LOCAL_FLAG=TMP.LOCAL_FLAG
AND BI.EFFDT=(
SELECT MAX(A.EFFDT)
FROM PS_BUS_UNIT_BI A
WHERE A.SETID=BI.SETID
AND A.ACCT_TYPE=BI.ACCT_TYPE
AND A.BUSINESS_UNIT=BI.BUSINESS_UNIT
AND A.PLACEMENT_TYPE=BI.PLACEMENT_TYPE
AND A.LOCAL_FLAG=BI.LOCAL_FLAG
AND A.EFFDT<=SYSDATE)
AND TMP.BUSINESS_UNIT_BI <> ' '
AND TRIM(BI.DEPTID) <> ' '
AND BI.DEPTID IS NOT NULL )
WHERE TMP.PROCESS_INSTANCE = %ProcessInstance
AND TMP.BATCH_STATUS = 'N'

Can someone let me know what I am missing?

Guest's picture
Guest
Re: ORA-01407: Cannot Update ('SYS ADM'.'PS STAGE ...

UPDATE %Table(PS_STAGE_TMP)TMP
SET TMP.DEPTID = (
SELECT BI.DEPTID
FROM PS_BUS_UNIT_BI BI
WHERE BI.BUSINESS_UNIT = TMP.BUSINESS_UNIT_BI
AND BI.PLACEMENT_TYPE=TMP.PLACEMENT_TYPE
AND BI.LOCAL_FLAG=TMP.LOCAL_FLAG
AND BI.EFFDT=(
SELECT MAX(A.EFFDT)
FROM PS_BUS_UNIT_BI A
WHERE A.SETID=BI.SETID
AND A.ACCT_TYPE=BI.ACCT_TYPE
AND A.BUSINESS_UNIT=BI.BUSINESS_UNIT
AND A.PLACEMENT_TYPE=BI.PLACEMENT_TYPE
AND A.LOCAL_FLAG=BI.LOCAL_FLAG
AND A.EFFDT<=SYSDATE)
AND TMP.BUSINESS_UNIT_BI <> ' '
AND TRIM(BI.DEPTID) <> ' '
AND BI.DEPTID IS NOT NULL )
WHERE TMP.PROCESS_INSTANCE = %ProcessInstance
AND TMP.BATCH_STATUS = 'N' AND
EXISTS (SELECT BI.DEPTID
FROM PS_BUS_UNIT_BI BI
WHERE BI.BUSINESS_UNIT = TMP.BUSINESS_UNIT_BI
AND BI.PLACEMENT_TYPE=TMP.PLACEMENT_TYPE
AND BI.LOCAL_FLAG=TMP.LOCAL_FLAG
AND BI.EFFDT=(
SELECT MAX(A.EFFDT)
FROM PS_BUS_UNIT_BI A
WHERE A.SETID=BI.SETID
AND A.ACCT_TYPE=BI.ACCT_TYPE
AND A.BUSINESS_UNIT=BI.BUSINESS_UNIT
AND A.PLACEMENT_TYPE=BI.PLACEMENT_TYPE
AND A.LOCAL_FLAG=BI.LOCAL_FLAG
AND A.EFFDT<=SYSDATE)
AND TMP.BUSINESS_UNIT_BI <> ' '
AND TRIM(BI.DEPTID) <> ' '
AND BI.DEPTID IS NOT NULL )

Guest's picture
Guest
Re: ORA-01407: Cannot Update ('SYS ADM'.'PS STAGE ...

your where clause below, is incorrect.
and trim(bi.deptid) <> ' '

If you are going to use the trim function, then check for null
ex. and trim(bi.deptid) is not null

If you are going to check for a blank character value, then check for a single blank space, as character fields are not allowed to be null in a peoplesoft system.
ex. and bi.deptid <> ' '

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
17 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.