convert access sql to oracle sql | Newbie | | Join Date: Aug 2009
Posts: 1
| | |
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_Title,1,99), t1.PlusUp = IIf(Mid(Trim(t2.frc_title),1,2)='(C',True,False), t1.Loa_Key = t2.loa_key, t1.FGLI = t2.fgli
Thanks!
|  | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,511
| | | re: convert access sql to oracle sql
you need to replace mid with substr and iif with case.
|  | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: convert access sql to oracle sql
Quote:
Originally Posted by 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_Title,1,99), t1.PlusUp = IIf(Mid(Trim(t2.frc_title),1,2)='(C',True,False), 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);
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
