Home » SQL & PL/SQL » SQL & PL/SQL » Help: Need to convert to PL/SQL
Help: Need to convert to PL/SQL [message #252457] Wed, 18 July 2007 21:59 Go to next message
npadilla
Messages: 9
Registered: July 2007
Junior Member
Hi
I need to convert this mySQL statement to PL/SQL
Thanks in advance


PROCEDURE `sp_report_ProductivityDaily`(in ProjectID varchar(4), in VersionID varchar(Cool, 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, Cool = ? 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
Re: Help: Need to convert to PL/SQL [message #252515 is a reply to message #252457] Thu, 19 July 2007 01:30 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

PL/SQL User's Guide and Reference is a nice book as well as SQL Reference.

Regards
Michel
Previous Topic: One time only procedure
Next Topic: need logic with decode
Goto Forum:
  


Current Time: Fri Dec 09 15:55:06 CST 2016

Total time taken to generate the page: 0.28438 seconds