MySQL Forums
Forum List  »  Oracle

Convert: mySQL to PL/SQL
Posted by: Nelson Padilla
Date: July 18, 2007 08:30PM

CREATE DEFINER=`root`@`localhost`

PROCEDURE `sp_report_ProductivityDaily`(in ProjectID varchar(4), in VersionID varchar(8), in DateFrom date, in DateTo date)

BEGIN
Declare prefix_stmt TEXT;
Declare SQL_stmt TEXT;
Declare myTable varchar(20) default 'ely';
SET prefix_stmt = '';
SET prefix_stmt = CONCAT(prefix_stmt, 'SELECT Prj.fld_Name as `Project Name`, V.fld_Name as `Version`, date(P.fld_date) as `DATE`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'S.fld_name as `STEP`, ' );
SET prefix_stmt = CONCAT(prefix_stmt, 'U.fld_Name as `User Name`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'count(P.fld_batch_ID) as `BATCHES`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_rec_count) as `RECORDS`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_field_count) as `FIELDS`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_char_count) AS `CHARACTERS`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_Duration as HOURS,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_char_count) / sum(fld_Duration) as SPEED,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_batch_quota,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_REC_quota,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_field_quota,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_char_quota' );
SET prefix_stmt = CONCAT(prefix_stmt, ' FROM db_proj_mngr.tbl_Productivity P ' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_user U' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON P.fld_user_id=U.fld_user_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_Step S' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON MID(P.fld_step_ver_id, 9,2)=S.fld_step_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_Project Prj' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON MID(P.fld_step_ver_id, 1,4)=Prj.fld_proj_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_Version V' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON MID(P.fld_step_ver_id, 1, 8)=V.fld_ver_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_step_of_version STV' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON STV.fld_step_ver_id = P.fld_step_ver_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' Where MID(P.fld_step_ver_id, 1,4)=? AND MID(P.fld_step_ver_id, 1, 8) = ? AND' );
SET prefix_stmt = CONCAT(prefix_stmt, ' date(fld_date) between ? AND ?' );
SET prefix_stmt = CONCAT(prefix_stmt, ' GROUP BY Prj.fld_Name, V.fld_Name, date(P.fld_date), S.fld_name, P.fld_user_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ORDER BY Prj.fld_Name, V.fld_Name, date(P.fld_date), S.fld_name, P.fld_user_id;' );
SET @SQL_stmt = prefix_stmt;
PREPARE stmt FROM @SQL_stmt;
SET @a = ProjectID;
SET @b = VersionID;
SET @c = DateFrom;
SET @d = DateTo;
EXECUTE stmt USING @a, @b, @c, @d;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
Convert: mySQL to PL/SQL
5298
July 18, 2007 08:30PM
2707
July 18, 2007 08:52PM


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.