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

[Dylan Beaudette]

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

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