
nine_grounds
New User
Dec 7, 2008, 2:48 PM
Post #1 of 6
(12976 views)
|
Combined SQL select / insert queries
|
Can't Post
|
|
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)
|