MySQL Forums
Forum List  »  Oracle

Need help with Oracle Migration of SQL
Posted by: Marc Lefebvre
Date: December 18, 2010 02:47AM

I am currently porting a web application to MySQL from Oracle and need help on two queries. What would be the resulting queries for MySQL?

CREATE or REPLACE VIEW vw_event_pivot AS
SELECT a.*, b.printorder, b.points
FROM (SELECT series_id, event_id, division_id, sailor_id, race_id, TO_CHAR(position, '90.9') AS position FROM tb_heat)
PIVOT (SUM(position) AS race FOR (race_id) IN (1 AS A, 2 AS B, 3 AS C, 4 AS D)) a, vw_event b
WHERE a.series_id=b.series_id
AND a.event_id=b.event_id
AND a.division_id=b.division_id
AND a.sailor_id=b.sailor_id
ORDER BY ABS(a.series_id), ABS(a.event_id), b.printorder, b.points;


CREATE or REPLACE VIEW vw_event_rank AS
SELECT RANK() OVER (PARTITION BY series_id, event_id, division_id ORDER BY points) rank,
series_id, event_id, division_id, printorder, sailor_id, a_race, b_race, c_race, d_race, TO_CHAR(points, '990.0') AS points
FROM vw_event_pivot
ORDER BY ABS(series_id), ABS(event_id), printorder, points;

Thank you...

Options: ReplyQuote

Written By
Need help with Oracle Migration of SQL
December 18, 2010 02:47AM

