473,326 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

convert access sql to oracle sql

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!
Aug 5 '09 #1
2 3896
debasisdas
8,127 Expert 4TB
you need to replace mid with substr and iif with case.
Aug 6 '09 #2
OraMaster
135 100+
@ivy317317
Please try below one. Since you have multiple colomns join between t1 and t2 query is big.

Expand|Select|Wrap|Line Numbers
  1. UPDATE t1
  2.    SET t1.pe =
  3.           (SELECT dodpe
  4.              FROM t2
  5.             WHERE t1.subhead = t2.subhead
  6.               AND t1.proj = t2.project
  7.               AND t1.pdli = t2.task
  8.               AND t1.department = t2.branch
  9.               AND t1.pm_code = t2.pgm_mgr_code
  10.               AND t1.parm = t2.parm_code
  11.               AND ROWNUM < 2),
  12.        t1.frc =
  13.           (SELECT SUBSTR (t2.frc, 1, 5)
  14.              FROM t2
  15.             WHERE t1.subhead = t2.subhead
  16.               AND t1.proj = t2.project
  17.               AND t1.pdli = t2.task
  18.               AND t1.department = t2.branch
  19.               AND t1.pm_code = t2.pgm_mgr_code
  20.               AND t1.parm = t2.parm_code
  21.               AND ROWNUM < 2),
  22.        t1.frc_title =
  23.           (SELECT SUBSTR (t2.frc_title, 1, 99)
  24.              FROM t2
  25.             WHERE t1.subhead = t2.subhead
  26.               AND t1.proj = t2.project
  27.               AND t1.pdli = t2.task
  28.               AND t1.department = t2.branch
  29.               AND t1.pm_code = t2.pgm_mgr_code
  30.               AND t1.parm = t2.parm_code
  31.               AND ROWNUM < 2),
  32.        t1.plusup =
  33.           (SELECT DECODE (SUBSTR (TRIM (t2.frc_title), 1, 2),
  34.                           '(C', TRUE,
  35.                           FALSE
  36.                          )
  37.              FROM t2
  38.             WHERE t1.subhead = t2.subhead
  39.               AND t1.proj = t2.project
  40.               AND t1.pdli = t2.task
  41.               AND t1.department = t2.branch
  42.               AND t1.pm_code = t2.pgm_mgr_code
  43.               AND t1.parm = t2.parm_code
  44.               AND ROWNUM < 2),
  45.        t1.loa_key =
  46.           (SELECT t2.loa_key
  47.              FROM t2
  48.             WHERE t1.subhead = t2.subhead
  49.               AND t1.proj = t2.project
  50.               AND t1.pdli = t2.task
  51.               AND t1.department = t2.branch
  52.               AND t1.pm_code = t2.pgm_mgr_code
  53.               AND t1.parm = t2.parm_code
  54.               AND ROWNUM < 2),
  55.        t1.fgli =
  56.           (SELECT t2.fgli
  57.              FROM t2
  58.             WHERE t1.subhead = t2.subhead
  59.               AND t1.proj = t2.project
  60.               AND t1.pdli = t2.task
  61.               AND t1.department = t2.branch
  62.               AND t1.pm_code = t2.pgm_mgr_code
  63.               AND t1.parm = t2.parm_code
  64.               AND ROWNUM < 2);
Aug 6 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Piyush Bichhoriya | last post by:
Hi, We are using earlier Pro*c to access the oracle database. now we are converting our application to use SQL server and need to convert all datalayers in C#. Is there a way to convery all...
1
by: ian m via SQLMonster.com | last post by:
Hi, I currently have a ms access update query that runs perfectly well and quicly in access however I now need to add this query to convert this qeryu to oracles equivelant sql syntax and add it...
3
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
13
by: BigDaDDY | last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going to be kicked back in my chair eating popcorn and watching football 10 years from now, while all you clowns are scrambling...
11
by: Rosco | last post by:
Does anyone have a good URL or info whre Oracle and Access are compared to one another in performance, security, cost etc. Before you jump on me I know Oracle is a Cadillac compared to Access the...
2
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with...
0
by: sebastiany | last post by:
Hi, I currently have a MS Access select query (pretty lengthy) that runs fine in MS Access, however I now need to convert this query to Oracle's equivelant SQL syntax so that I can create a SQL...
3
by: abeerhassen | last post by:
hi please tell me how to convert MS access DB to oracle 8i DB
5
by: WPW07 | last post by:
Hello, We have several complex applications developed in Access 2003 by various outside consultants. These applications link to a variety of Oracle tables and are used only for Access reports. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.