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?
Title | Under | Posted on |
---|---|---|
Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
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 )
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 <> ' '