CODEWITHSUNDEEP
mysql
Ethan Graybeal
Ethan Graybeal

Reputation: 108

I am trying to create a temporary table in a stored procedure but I am getting an Error Code: 1060

This is the first time I have attempted to use temporary tables. Any help would be appreciated. My procedure looks like this

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `DailyTempTest`(startDate varchar(255), endDate varchar(255))
    DETERMINISTIC
begin
CREATE TEMPORARY TABLE IF NOT EXISTS dailyTemp as (
select * from Item 
join Bill on Bill.id = Item.bill_id
join Payment_Item pi on Item.id = pi.billItems_id  
join PaymentLine pl on pl.id = pi.PaymentLine_id 
left join Payment p on p.id = pl.payment_id 
    and p.payDate > '2017-07-31' 
left join Batch b on b.id = p.Batch 
    and b.posted = 1);

select  gl.accountNumber account, gl.description, sum(amount)  
from dailyTemp ;

DROP TEMPORARY TABLE IF EXISTS dailyTemp;


end$$
DELIMITER ;

I am getting an Error Code: 1060 Duplicate column name 'id'. Am I not able to use joins when creating a temp table?

Upvotes: 0

Views: 102

Answers (1)

adevel
adevel

Reputation: 59

It seems that you must explicit declare what "table.fields" you want to take in "select * from Item" because when you select with the join is taking all fields of both tables and both tables have id.

PS : Is easy to check and help if you give the create table script for all tables involved in the problem so anyone can create it and test.

Upvotes: 1

Related Questions