[sqr-users] Update query

["Pradnya Jawale" <pradnya.jawale@patni.com>]

Hi all,

I am trying to update supervisor is and name in employees table with
following query.

UPDATE PS_EMPLOYEES
  SET (SUPERVISOR_ID,SUPERVISOR_NAME) = (
 SELECT SUP.EMPLID
 , SUP.NAME
  FROM PS_PERSONAL_DATA SUP
  , PS_JOB J
 WHERE J.POSITION_NBR = PS_EMPLOYEES.REPORTS_TO
   AND J.EMPLID = SUP.EMPLID
   AND PS_EMPLOYEES.REPORTS_TO != ' '
   AND J.EMPL_RCD = 0
   AND PS_EMPLOYEES.EMPL_STATUS IN ('A','P','L','S','T','U','R')
   AND J.EFFDT = (
 SELECT MAX(C.EFFDT)
  FROM PS_JOB C
 WHERE C.EMPLID = J.EMPLID
   AND C.EMPL_RCD = J.EMPL_RCD
   AND C.EFFDT <= SYSDATE)
   AND J.EFFSEQ = (
 SELECT MAX(D.EFFSEQ)
  FROM PS_JOB D
 WHERE D.EMPLID = J.EMPLID
   AND D.EMPL_RCD = J.EMPL_RCD
   AND D.EFFDT = J.EFFDT
   AND J.EMPL_STATUS IN ('A','P','L','S') ) )
 WHERE EMPLID IN (
 SELECT JJ.EMPLID
  FROM PS_JOB JJ
 WHERE JJ.EMPLID = PS_EMPLOYEES.EMPLID
   AND PS_EMPLOYEES.REPORTS_TO != ' '
   AND PS_EMPLOYEES.EMPL_STATUS IN ('A','P','L','S','T','U','R')
   AND JJ.EFFDT = (
 SELECT MAX(CC.EFFDT)
  FROM PS_JOB CC
 WHERE CC.EMPLID = JJ.EMPLID
   AND CC.EMPL_RCD = JJ.EMPL_RCD
   AND CC.EFFDT <= SYSDATE)
   AND JJ.EFFSEQ = (
 SELECT MAX(DD.EFFSEQ)
  FROM PS_JOB DD
 WHERE DD.EMPLID = JJ.EMPLID
   AND DD.EMPL_RCD = JJ.EMPL_RCD
   AND DD.EFFDT = JJ.EFFDT
   AND JJ.EMPL_STATUS IN ('A','P','L','S') ) )

If I hardcode some employee then it runs fine. But otherwise it gives me
following error:

UPDATE PS_EMPLOYEES
*
ERROR at line 1:
ORA-01407: cannot update ("PRJ"."PS_EMPLOYEES"."SUPERVISOR_ID") to NULL

I checked the data in the source tables and there are no issues with the
data.
Anybody faced this error earlier.
Thanks in advance.
Regards,
Pradnya.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users