当前位置: 首页 > 知识库问答 >
问题:

MySQL说:#1064-你的SQL语法有错误;在第97行的“”附近使用正确语法的MySQL服务器版本

戚晨
2023-03-14

这是一个已经用自己的语法运行SQLServer2005的存储过程,我在这里用mysql语法编写了这个存储过程。一切正常,但最后一个结束语句显示错误。

定界符$$

创建定义者=root@localhost程序sp_PREPAREACCOUNTREPORTSBEGIN(在fdate1DATE,在tdate1DATE,在grossrofitDOUBLE,在OpeningStockDOUBLE,在ClosingStockDOUBLE,在diffalanceDOUBLE,在
dcVARCHAR(25)开始)

Create temporary table if not exists Trading
        (Ac_code bigint, AcName varchar(255),Amount float,DC varchar(20),seq int) ENGINE=MEMORY;
insert into Trading SELECT  ledger_master.Ac_code, ledger_master.Ac_name,
                (select case  tmp_closingbalance.Dr when 0 then tmp_closingbalance.Cr else tmp_closingbalance.Dr end),  (select case  tmp_closingbalance.Dr when 0 then 'cr' else 'dr' end),sequence
FROM ledger_master INNER JOIN tmp_closingbalance ON ledger_master.Ac_code = tmp_closingbalance.ac_code INNER JOIN accountgroup ON ledger_master.Group_code = accountgroup.Group_code
where accountgroup.position='t' order by sequence;
set @GrossProfit := 0;
set @GrossProfit := (select ifnull(sum(Amount),0) from Trading where DC='dr') - (select ifnull(sum(Amount),0) from Trading where DC='cr');
        if(@GrossProfit >0) 
        THEN    
        insert into Trading values(0,'GROSS PROFIT',  (@GrossProfit),'cr',9999 );
        else if(@GrossProfit <0)
        then
        insert into Trading values(0,'GROSS PROFIT',  -(@GrossProfit),'dr',9999 );
         end if;
Create temporary table if not exists PL
        (Ac_code bigint,AcName varchar(255),Amount float,DC varchar(20),seq int) ENGINE=MEMORY;
insert into PL 
SELECT    ledger_master.Ac_code, ledger_master.Ac_name,
                (select case  tmp_closingbalance.Dr when 0 then tmp_closingbalance.Cr else tmp_closingbalance.Dr end),
                (select case  tmp_closingbalance.Dr when 0 then 'cr' else 'dr' end),sequence
FROM         ledger_master INNER JOIN
                      tmp_closingbalance ON ledger_master.Ac_code = tmp_closingbalance.ac_code INNER JOIN
                      accountgroup ON ledger_master.Group_code = accountgroup.Group_code
where accountgroup.position='p' order by sequence;
        if (@GrossProfit >0)
        then
        insert into PL values(0,'BY GROSS PROFIT',  (@GrossProfit),'dr',9999 );
        else if (@GrossProfit <0)
        then
        insert into PL values(0,' BY GROSS PROFIT',  -(@GrossProfit),'cr',9999 );
        end if;
set @NetProfit := 0;
set @NetProfit := (select ifnull(sum(Amount),0) from PL where DC='dr') - (select ifnull(sum(Amount),0) from PL where DC='cr');
        if(@NetProfit >0)
        then    
insert into PL values(0,'TO NET PROFIT',  (@NetProfit),'cr',9999 );
else if(@NetProfit <0)
then
insert into PL values(0,'TO NET PROFIT',  -(@NetProfit),'dr',9999 );
end if;

Create temporary table if not exists Balancesheet
        (Ac_code bigint,AcName varchar(255),Amount float,DC varchar(20),seq int) ENGINE=MEMORY;
insert into Balancesheet 
SELECT     ledger_master.Ac_code,ledger_master.Ac_name,
                (select case  tmp_closingbalance.Dr when 0 then tmp_closingbalance.Cr else tmp_closingbalance.Dr end),  (select case  tmp_closingbalance.Dr when 0 then 'cr' else 'dr' end),sequence
FROM  ledger_master INNER JOIN tmp_closingbalance ON ledger_master.Ac_code = tmp_closingbalance.ac_code INNER JOIN accountgroup ON ledger_master.Group_code = accountgroup.Group_code
where accountgroup.position='b' order by sequence;

insert into Balancesheet values(0,'PROFIT & LOSS A/C',-(@NetProfit),'cr',9999);
set @DiffInBalance := 0;
select @DiffInBalance := ((select ifnull(sum(Amount),0) from Balancesheet where DC='dr') - ( select ifnull(sum(Amount),0) from Balancesheet where DC='cr'));

delete from tmp_prepareaccountreports; 
insert into tmp_prepareaccountreports select Ac_code,AcName,Amount,DC,seq,1 as types from Trading order by  types,seq;
insert into tmp_prepareaccountreports select Ac_code,AcName,Amount,DC,seq,2 as types from PL order by  types,seq;
insert into tmp_prepareaccountreports select Ac_code,AcName,Amount,DC,seq,3 as types from Balancesheet order by types,seq;

结束$$分隔符;

共有1个答案

吉俊德
2023-03-14

如果没有,请查看https://dev.mysql.com/doc/refman/8.0/en/if.html

 类似资料: