HELP Translating ORACLE > MYSQL
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