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

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.