468,554 Members | 1,087 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,554 developers. It's quick & easy.

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 6315
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
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.