471,049 Members | 1,635 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

convert ms access sql staement to oracle sql statement

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 to the end
of an oracle sql script.

Unfortunately Im not having much success although i seem to be able to
convert it to a working oracle sql. it takes hours to run the statement in
oracle where as in access it runs in seconds

any help is appreciated.

Ms Access sql :

UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.
STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON
(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND
(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND
(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND
(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)
SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,
PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tprice
WHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));

Oracle SQL :
update pro.tst_rv3x_rpt_wrk x
set(x.mkd_date, x.mkd_price) =
(Select a.new_active_date, a.new_tprice
from pro.sty_tprices a, pro.style_colours b
where a.sty_id=b.sty_id
and b.bf_mat_char_val = x.bf_mat_char_val
and b.season = x.season
and b.sty_num = x.sty_num
and b.sty_qual = x.sty_qual
and a.new_active_date is not null
)
Aug 31 '05 #1
1 6428
ian m via SQLMonster.com (fo***@SQLMonster.com) writes:
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 to the end of an
oracle sql script.

Unfortunately Im not having much success although i seem to be able to
convert it to a working oracle sql. it takes hours to run the statement in
oracle where as in access it runs in seconds


Without knowledge about the tables, their sizes and the and indexes it is
impossible to tell.

But, stop! don't post that information as a reply to this post. There is
one more thing that is wrong with your post: you are posting to a newsgroup
for SQL Server. What is truth on SQL Server may not be good on Oracle.
So try comp.databases.oracle instead.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 31 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by BigDaDDY | last post: by
1 post views Thread by Andrew Arace | last post: by
8 posts views Thread by Alfonso Esteban Gonzalez Sencion | last post: by
7 posts views Thread by peter.morin | last post: by
2 posts views Thread by egoldthwait | last post: by

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.