Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle-SQL update statement?

Re: Oracle-SQL update statement?

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 29 Sep 2006 14:28:59 -0700
Message-ID: <1159565339.459839@bubbleator.drizzle.com>


mike_at_zowee.com wrote:
> 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)
This is a bad SQL statement in any product or language variation.

I would suggest you start out by writing the business rules in pseudocode. Then pick up an Oracle SQL book, or use http://tahiti.oracle.com and write code to the rules.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Fri Sep 29 2006 - 16:28:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US