MySQL Forums
Forum List  »  Oracle

HELP Translating ORACLE > MYSQL
Posted by: Yuri Regueiro
Date: April 02, 2009 11:16AM

Hi,
In trying to migrate an oracle script into mysql and there are some lines where my head goes blank so maybe you guys can help

a quick overview of what the script does is get data from Xdata table creates a table with that then it "creates" another table with values and merges into a new table. so lest say i got data on month 1 and month 3 but none on month 2 what this does is show me all months 1,2,3 and fills month 2 with 00000s.
ORACLE CODE:
-----------------------------------------------------------------------
with
results as (
SELECT
TO_CHAR(fld_time,'MON-YYYY') AS f_Month,
TO_CHAR(count(distinct TO_CHAR(fld_time,'DD-MON-YYYY')),'FM00') AS f_Days_Used,
TO_CHAR(count(distinct fld_Workstation),'FM0000') AS f_Workstations,
TO_CHAR(count(distinct fld_Model),'FM0000') AS f_Models,
TO_CHAR(count(distinct fld_esn),'FM00000000') AS f_Total_Phones,
TO_CHAR(count (fld_index),'FM00000000') AS f_Total_Clicks,
TO_CHAR(max(fld_index),'FM00000000') as f_Last_Index,
max(fld_x) keep (dense_rank last order by fld_index) as f_X
from
OPS$somedatabase.LOG
where
fld_esn is not null
group by
TO_CHAR(fld_time,'MON-YYYY'),
TO_CHAR(fld_time,'MM')
order by f_Month,TO_CHAR(fld_time,'MM')
)
SELECT
MONTHLIST.f_month,
COALESCE(results.f_Days_Used,'00') as f_Days_Used,
COALESCE(results.f_Workstations,'0000') as f_Workstations,
COALESCE(results.f_Models,'0000') as f_Models,
COALESCE(results.f_Total_Phones,'00000000') as f_Total_Phones,
COALESCE(results.f_Total_Clicks,'00000000') as f_Total_Clicks,
COALESCE(results.f_Last_Index,'00000000') f_Last_Index,
results.f_X
FROM(
SELECT
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),LEVEL-10),'MON-YYYY') f_MONTH,
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),LEVEL-10),'YYYYMM') f_MONTHNUMBER
FROM dual
CONNECT BY LEVEL <= 12
) MONTHLIST
LEFT JOIN RESULTS ON MONTHLIST.F_MONTH=RESULTS.F_MONTH
ORDER BY MONTHLIST.f_MONTHNUMBER
------------------------------------------------------------
MYSQL CODE:
DROP VIEW IF EXISTS `RESULTS`;
CREATE VIEW `RESULTS` as SELECT
DATE_FORMAT(`FLD_TIME`,'%b - %Y') as `f_month`,
LPAD(count(distinct DATE_FORMAT(`FLD_TIME`,'%d-%b-%Y')),2,'0') as `f_Days_Used`,
LPAD(count(distinct(`FLD_WORKSTATION`)),4,'0') as `f_Workstations`,
LPAD(count(distinct(`FLD_MODEL`)),4,'0') as `f_Models`,
LPAD(count(distinct(`FLD_ESN`)),8,'0')as `f_Total_Phones`,
LPAD(count(`FLD_INDEX`),8,'0') as `f_Total_Clicks`,
LPAD(max(`FLD_INDEX`),8,'0')as `f_last_Index`,
max(`FLD_X`) as `f_x`
FROM
`masterlog`
WHERE `FLD_ESN` is not null
group by
DATE_FORMAT(`FLD_TIME`,'%b-%Y'),
DATE_FORMAT(`FLD_TIME`,'%b')
order by
EXTRACT(MONTH FROM `FLD_TIME`)ASC;
SELECT
`MONTHLIST`.`f_month`,
COALESCE(`RESULTS`.`f_Days_Used`,'00') as `f_Days_Used`,
COALESCE(`RESULTS`.`f_Workstations`,'0000') as `f_Workstations`,
COALESCE(`RESULTS`.`f_Models`,'0000') as `f_Models`,
COALESCE(`RESULTS`.`f_Total_Phones`,'00000000') as `f_Total_Phones`,
COALESCE(`RESULTS`.`f_Total_Clicks`,'00000000') as `f_Total_Clicks`,
COALESCE(`RESULTS`.`f_last_Index`,'00000000') as `f_last_Index`,
`RESULTS`.`f_X`
--------------------------------------------------------------------
so far i got everything migrated But this(because there are som oracle specific codes)

FROM(
SELECT
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),LEVEL-10),'MON-YYYY') f_MONTH,
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),LEVEL-10),'YYYYMM') f_MONTHNUMBER
FROM dual
CONNECT BY LEVEL <= 12
) MONTHLIST
LEFT JOIN RESULTS ON MONTHLIST.F_MONTH=RESULTS.F_MONTH
ORDER BY MONTHLIST.f_MONTHNUMBER

thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
HELP Translating ORACLE > MYSQL
6193
April 02, 2009 11:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.