convert access sql to oracle sql

[ivy317317]

Can someone help me convert this Access query to Oracle SQL?

UPDATE t1 INNER JOIN t2 ON (t1.begin_year = t2.FY) AND (t1.subhead = t2.SUBHEAD) AND (t1.proj = t2.PROJECT) AND (t1.pdli = t2.TASK) AND (t1.Department = t2.BRANCH) AND (t1.PM_Code = t2.PGM_MGR_CODE ) AND (t1.Parm = t2.PARM_CODE) SET t1.PE = t2.dodpe, t1.FRC = Mid(t2.FRC,1,5) , t1.FRC_Title = Mid(t2.FRC_Titl e,1,99), t1.PlusUp = IIf(Mid(Trim(t2 .frc_title),1,2 )='(C',True,Fal se), t1.Loa_Key = t2.loa_key, t1.FGLI = t2.fgli

Thanks!

[debasisdas]

you need to replace mid with substr and iif with case.

[OraMaster]

Can someone help me convert this Access query to Oracle SQL?

UPDATE t1 INNER JOIN t2 ON (t1.begin_year = t2.FY) AND (t1.subhead = t2.SUBHEAD) AND (t1.proj = t2.PROJECT) AND (t1.pdli = t2.TASK) AND (t1.Department = t2.BRANCH) AND (t1.PM_Code = t2.PGM_MGR_CODE ) AND (t1.Parm = t2.PARM_CODE) SET t1.PE = t2.dodpe, t1.FRC = Mid(t2.FRC,1,5) , t1.FRC_Title = Mid(t2.FRC_Titl e,1,99), t1.PlusUp = IIf(Mid(Trim(t2 .frc_title),1,2 )='(C',True,Fal se), t1.Loa_Key = t2.loa_key, t1.FGLI = t2.fgli

Thanks!

Please try below one. Since you have multiple colomns join between t1 and t2 query is big.

UPDATE t1
   SET t1.pe =
          (SELECT dodpe
             FROM t2
            WHERE t1.subhead = t2.subhead
              AND t1.proj = t2.project
              AND t1.pdli = t2.task
              AND t1.department = t2.branch
              AND t1.pm_code = t2.pgm_mgr_code
              AND t1.parm = t2.parm_code
              AND ROWNUM < 2),
       t1.frc =
          (SELECT SUBSTR (t2.frc, 1, 5)
             FROM t2
            WHERE t1.subhead = t2.subhead
              AND t1.proj = t2.project
              AND t1.pdli = t2.task
              AND t1.department = t2.branch
              AND t1.pm_code = t2.pgm_mgr_code
              AND t1.parm = t2.parm_code
              AND ROWNUM < 2),
       t1.frc_title =
          (SELECT SUBSTR (t2.frc_title, 1, 99)
             FROM t2
            WHERE t1.subhead = t2.subhead
              AND t1.proj = t2.project
              AND t1.pdli = t2.task
              AND t1.department = t2.branch
              AND t1.pm_code = t2.pgm_mgr_code
              AND t1.parm = t2.parm_code
              AND ROWNUM < 2),
       t1.plusup =
          (SELECT DECODE (SUBSTR (TRIM (t2.frc_title), 1, 2),
                          '(C', TRUE,
                          FALSE
                         )
             FROM t2
            WHERE t1.subhead = t2.subhead
              AND t1.proj = t2.project
              AND t1.pdli = t2.task
              AND t1.department = t2.branch
              AND t1.pm_code = t2.pgm_mgr_code
              AND t1.parm = t2.parm_code
              AND ROWNUM < 2),
       t1.loa_key =
          (SELECT t2.loa_key
             FROM t2
            WHERE t1.subhead = t2.subhead
              AND t1.proj = t2.project
              AND t1.pdli = t2.task
              AND t1.department = t2.branch
              AND t1.pm_code = t2.pgm_mgr_code
              AND t1.parm = t2.parm_code
              AND ROWNUM < 2),
       t1.fgli =
          (SELECT t2.fgli
             FROM t2
            WHERE t1.subhead = t2.subhead
              AND t1.proj = t2.project
              AND t1.pdli = t2.task
              AND t1.department = t2.branch
              AND t1.pm_code = t2.pgm_mgr_code
              AND t1.parm = t2.parm_code
              AND ROWNUM < 2);