CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI: Combined SQL select / insert queries: Edit Log



nine_grounds
New User

Dec 7, 2008, 2:48 PM


Views: 5638
Combined SQL select / insert queries

Hello,

I am trying to get a query to work in Perl, but it can't seem to handle it and I don't really know why. I am using DBI, and it gives me a MySQL syntax error at the start of the second part. The query has 4 parts as follows:

CREATE TEMPORARY TABLE TempTimesheet ( jobnum int, job_sub_num varchar(5), lname varchar(100), fname varchar(100), type varchar(6), std_charge_rate decimal(5,2), hours decimal(5,2), rate decimal(5,2) );

This creates a temporary table with all the fields I require. The next part take values from other tables (`Timesheets_Jobs` and `employees`) and inserts filtered results into the temporary table as follows:

INSERT INTO TempTimesheet (jobnum, lname, fname, type, std_charge_rate, hours, rate) SELECT `Timesheets_Jobs`.`jobnum`, `employees`.`lname`, `employees`.`fname`, `employees`.`type`, `employees`.`std_charge_rate`, `Timesheets_Jobs`.`hours`, `Timesheets_Jobs`.`rate` FROM `Timesheets_Jobs` LEFT OUTER JOIN `employees` ON `Timesheets_Jobs`.`employee` = `employees`.`number` WHERE `Timesheets_Jobs`.`fiscal_week` = 20080903;

Followed by a similar insert command, that uses the tables `Timesheets_Job_Parts` and `employees`, as follows:

INSERT INTO TempTimesheet (jobnum, job_sub_num, lname, fname, type, std_charge_rate, hours, rate) SELECT `Timesheets_Job_Parts`.`jobnum`, `Timesheets_Job_Parts`.`job_sub_num`, `employees`.`lname`, `employees`.`fname`, `employees`.`type`, `employees`.`std_charge_rate`, `Timesheets_Job_Parts`.`hours`, `Timesheets_Job_Parts`.`rate` FROM `Timesheets_Job_Parts` LEFT OUTER JOIN `employees` ON `Timesheets_Job_Parts`.`employee` = `employees`.`number` WHERE `Timesheets_Job_Parts`.`fiscal_week` = 20080903;

Note that "20080903" is a value that gets determined by variables selected by the user. The last part of the query selects the data from the temporary table (using a reference to a table called `jobs`) and sorts it as I require:

SELECT `jobs`.`bid_status`, `TempTimesheet`.`jobnum`, `jobs`.`client_code`, `TempTimesheet`.`job_sub_num`, `TempTimesheet`.`lname`, `TempTimesheet`.`fname`, `TempTimesheet`.`hours`, `TempTimesheet`.`rate`, `TempTimesheet`.`type` FROM jobs, TempTimesheet WHERE `jobs`.`jobnum` = `TempTimesheet`.`jobnum` ORDER BY `jobnum` DESC, `job_sub_num` ASC, `type` ASC, `std_charge_rate` DESC, `lname` ASC

The query itself works fine in MySQL command line, and in phpMyAdmin, and typically runs in under 2 mS, but it seems that DBI has a problem with multiple steps like this. I have also tried including the word "VALUES", and brackets, before the selections for inserting, but to no avail.

Any help would be appreciated...

Thanks :)


(This post was edited by nine_grounds on Dec 7, 2008, 8:15 PM)


Edit Log:
Post edited by nine_grounds (New User) on Dec 7, 2008, 8:10 PM: To make SQL statement more readable
Post edited by nine_grounds (New User) on Dec 7, 2008, 8:15 PM: Better explain the exact problem


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives