Can someone convert this to an Oracle-SQL update statement? The goal
is to update tblWORKER.PER_TYPE based on the case statement. The
essentials are there but I haven't been able to get the correct syntax.
It was works until the WHERE EXISTS clause is added. Any ideas?
UPDATE PS_RB_WORKER
SET PER_TYPE = (SELECT
CASE WHEN SUBSTR(MYTABLE.WORKER_NAME, 1, 5) = 'OnCal' THEN 'O'
WHEN SUBSTR(MYTABLE.WORKER_NAME, 1, 5) = 'Depot' THEN 'P'
WHEN MYTABLE.WORKER_JOBCODE = 'FSTA' AND MYTABLE.COMPANYID <> ' ' AND
MYTABLE.FST_ID = ' ' THEN 'I'
WHEN MYTABLE.WORKER_JOBCODE = 'FSTA' AND MYTABLE.COMPANYID <> ' ' AND
MYTABLE.FST_ID <> ' ' THEN 'A'
WHEN MYTABLE.WORKER_JOBCODE = 'FST' AND MYTABLE.COMPANYID = ' ' AND
MYTABLE.FST_ID <> ' ' THEN 'D'
ELSE PER_TYPE END
FROM
(SELECT
PERSON.WORKER_NAME ,
PERSON.WORKER_STATUS,
PERSON.WORKER_PID,
PERSON.FST_ID,
PERSON.REG_TEMP,
PERSON.WORKER_JOBCODE,
PERSON.CURRENT_WORKER_TYPE,
REL.COMPANYID,
REL.COMPANY_NAME,
REL.RELATIONSHIP
FROM
(SELECT
B.NAME AS WORKER_NAME,
a.EMPL_STATUS as WORKER_STATUS,
B.PERSON_ID AS WORKER_PID,
C.EMPLID AS FST_ID,
A.REG_TEMP AS REG_TEMP,
A.JOBCODE AS WORKER_JOBCODE,
A.PER_TYPE AS CURRENT_WORKER_TYPE
FROM
PS_RB_WORKER A,
PS_RD_PERSON_NAME B,
PS_RD_PERSON C
WHERE
A.PERSON_ID = B.PERSON_ID
AND A.PERSON_ID = C.PERSON_ID (+)
AND B.PRIMARY_IND = 'Y'
AND A.PER_TYPE = ' ')PERSON,
(SELECT
CO.COMPANYID
,PER.PERSON_ID PID
FROM
PS_RD_COMPANY CO
,PS_RD_PERSON PER
WHERE
CO.PERSON_ID = PER.PERSON_ID) REL
WHERE
PERSON.WORKER_PID = REL.PID (+)) MYTABLE
WHERE EXISTS (SELECT 1 FROM PS_RB_WORKER AA WHERE MYTABLE.PERSON_ID =
AA.PERSON_ID)
Received on Fri Sep 29 2006 - 11:57:23 CDT
