l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
November 4: Social gathering
Next Installfest:
TBD
Latest News:
Oct. 24: LUGOD election season has begun!
Page last updated:
2004 May 10 12:49

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
[vox-tech] oracle to mysql conversion (revisited)
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[vox-tech] oracle to mysql conversion (revisited)



Greetings,

I would like to thank everyone who contributed ideas toward our conversion
process. We have decided on MySQL as the replacement for our Oracle 8.1.6
database. (The 50+ GB pesticide use database)

we have been able to convert the DB into MySQL style tables, and all seems
to be well in that respect. However, we are looking to hire someone to
help with the conversion of Oracle-style queries into corrosponding
MySQL-style syntax... There are quite a few of them, and it might require
a couple hours of work.

Thank in advance,

Dylan

PS: Here is common example of a series of queries that needs to be
converted to MySQL syntax:

----------------------------------------------------------------
Create table rrior_af Storage (initial 10000000 next 1000000) AS select
unique(agfield),
avg(acre_planted) acre_planted from vh213warrior_Q1 group by (agfield)

Create table rrior_fl Storage (initial 10000000 next 1000000) AS select
unique(to_char(chem_code)||','||agfield) conc_field, chem_code, agfield
from vh213warrior_Q1


Create table rrior_ap Storage (initial 10000000 next 1000000) AS select
rrior_fl.chem_code,
sum(rrior_af.acre_planted) acre_planted from rrior_af,rrior_fl where
rrior_af.agfield =
rrior_fl.agfield group by chem_code

Create table rrior_sun Storage (initial 10000000 next 1000000) AS SELECT
use_no, sum(lbs_chm_used)
lbs_chm_used, avg(lbs_prd_used) lbs_prd_used, avg(acre_treated)
acre_treated,  avg(applic_cnt)
applic_cnt from vh213warrior_Q1 where chem_code <> 0 GROUP BY use_no

Create table rrior_unl Storage (initial 10000000 next 1000000) AS select
unique(to_char(chem_code)||','||to_char(use_no)) conc_field, chem_code,
use_no from vh213warrior_Q1

Create table rrior_alt Storage (initial 10000000 next 1000000) AS select
rrior_unl.chem_code chem_code,
sum(rrior_sun.lbs_chm_used) lbs_chm_used, sum(rrior_sun.lbs_prd_used)
lbs_prd_used,
sum(rrior_sun.acre_treated) acre_treated, sum(rrior_sun.applic_cnt)
applic_cnt from rrior_sun,
rrior_unl where rrior_sun.use_no = rrior_unl.use_no group by chem_code

SELECT rrior_alt.chem_code, tb_chemical.chemname, Lbs_Used1.lbs_chm_used,
rrior_alt.lbs_prd_used,
rrior_alt.acre_treated, rrior_alt.applic_cnt, rrior_ap.acre_planted,
(lbs_used1.lbs_chm_used/(rrior_ap.acre_planted + 0.0000001)) rate_chm_plnt,
(lbs_used1.lbs_chm_used/(rrior_alt.acre_treated + 0.0000001)) rate_chm_trt
from rrior_ap,rrior_alt,
lbs_used1, tb_chemical where rrior_alt.chem_code = rrior_ap.chem_code and
rrior_alt.chem_code =
tb_chemical.chem_code and rrior_alt.chem_code = lbs_used1.chem_code

----------------------------------------------------------------


_______________________________________________
vox-tech mailing list
vox-tech@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech


LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Sunset Systems
Who graciously hosts our website & mailing lists!