Re: Syntax help migrating MS SQL Server to Oracle

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 16 Nov 2006 18:47:25 -0800
Message-ID: <1163731645.351885.271220@e3g2000cwe.googlegroups.com>

fgsdfgsdf wrote:
> Hi all
>
> I have the update statement written for MS SQL 2000 below and need to
> convert it to Oracle 9. It seems like Oracle does not support joins in
> update queries :(
> How can I do this in Oracle 9?
>
> Thanks in advance
> Stefan
>
> update t
> set t.SRights = case when ((f1.Id is null and f2.Id is null) or f2.Id is
> not null) then 1 else 0 end
> from GuiTmp t
> join ReadRights r on r.ObjectType = t.ObjectType and
> r.Status = t.Status
> join Access_ a on r.Access_ = a.Access_
> join ObjTypeAcc oa on oa.Access_ = a.Access_ and
> oa.ObjectType = r.ObjectType and
> oa.Cfg = r.Cfg
> left join FieldAcc f1 on oa.Id = f1.ObjectTypeAccess and
> f1.Cfg = 1 and
> f1.Rights = 1 and
> f1.Field = t.Field and
> f1.Status = t.Status
> left join FieldAcc f2 on oa.Id = f2.ObjectTypeAccess and
> f2.Cfg = 1 and
> f2.Rights in (2,3) and
> f2.Field = t.Field and
> f2.Status = t.Status
> where r.Cfg = 1 and
> t.RRights = -1

Did that statement actually run on SQLServer 2000?

I tried to make sense of what the SQL statement is trying to do, and converted it to Oracle syntax. Please test!

UPDATE
  T
SET
   T.SRIGHTS = (
SELECT
  SIGN(NVL2(F1.ID,0,1) * NVL2(F2.ID,0,1) + NVL2(F2.ID,1,0)) FROM
  GUITMP T,
  READRIGHTS R,
  ACCESS A,
  FIELDACC F1,
  FIELDACC F2
WHERE
  R.OBJECTTYPE = T.OBJECTTYPE
  AND R.STATUS = T.STATUS
  AND R.ACCESS = A.ACCESS

  AND OA.OBJECTTYPE = R.OBJECTTYPE
  AND OA.CFG = R.CFG
  AND OA.ID = F1.OBJECTTYPEACCESS(+)
  AND F1.CFG(+) = 1
  AND F1.RIGHTS(+) = 1
  AND F1.FIELD(+) = T.FIELD
  AND F1.STATUS(+) = T.STATUS
  AND OA.ID = F2.OBJECTTYPEACCESS(+)
  AND F2.CFG(+) = 1

  AND NVL(F2.RIGHTS,2) IN (2,3)
  AND F2.FIELD(+) = T.FIELD
  AND F2.STATUS(+) = T.STATUS
  AND R.CFG = 1
  AND T.RRIGHTS = -1); Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Nov 16 2006 - 20:47:25 CST