Click here to Skip to main content
15,887,822 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi,
please, i have a problem when i try to add UNION to my query in Mysql:
error : MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union (select StrYearr,StrDATE,0,3)

What I have tried:

code (Procedure):
<pre lang="SQL">
begin
DECLARE Mont INT default 1;
DECLARE StrDATE varchar(7) ;
DECLARE StrYearr varchar(7) ;
set StrYearr = StrYear ;
set StrDATE =  CONCAT(Mont,'-',StrYearr);

drop temporary table if exists TableHelp;
create temporary table TableHelp
(
    PeriodY year,
    PeriodM varchar(10),
    MtnSTaxes float,
    TypeEntr int
);
while (Mont <=12) do
    insert into TableHelp(PeriodY,PeriodM,MtnSTaxes,TypeEntr)
    (
     select (SecTabDatePaieY,SecTabDatePaieM,sum(SecTable.SumV_Prix),3)
     from (select Date_Format(p.DatePaie,"%Y")as SecTabDatePaieY,Date_Format(p.DatePaie,"%m-%Y")as SecTabDatePaieM,(select sum(V_Prix) from lsthelpget_montsansfrais where V_configYesNon=1 and V_IdPaie= p.IdPaie and V_IdFAct=p.IdFact) as SumV_Prix from paiement p where PayePaie=1) as SecTable
     where SecTable.SecTabDatePaieM=Date_Format(StrDATE,"%m-%Y") group by SecTable.SecTabDatePaieM asc UNION (select StrYearr,StrDATE,0,3)       
    );   
    set Mont= Mont+1 ;
END WHILE;
SELECT PeriodM,MtnSTaxes,TypeEntr from TableHelp;
end
Posted
Comments
CHill60 23-Jan-18 17:26pm    
Have you tried giving that sub query an alias?
Member 13559666 23-Jan-18 17:39pm    
i was given to him name 'as SecTable' but nothings
Member 13559666 23-Jan-18 17:40pm    
the problem just in UNION (select StrYearr,StrDATE,0,3)
CHill60 24-Jan-18 3:59am    
You've got bigger problems than just that UNION! Rarely if ever should you use a loop for set operations (mySql is set based), and you have far too many correlated sub-queries
You need to surround each of your sub-queries with parentheses and give each one an alias. I'd attempt to show you but without sample data it would take too long to unravel what you have there.For starters try using
UNION (select StrYearr,StrDATE,0,3) AS T1
Member 13559666 25-Jan-18 11:50am    
Thanks , I appreciate your help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900