1.转换MYSQL语法,转换错误函数如下:
错误提示: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 'return(TmpDays); end' at line 1 CREATE Function GetSatOrSunDay
(Types int,
SDates varchar(10),
Edates varchar(10))
Returns int
begin
declare TmpDays int;
select (case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end +
IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0,
datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) as tempfield
FROM tempdata INTO TmpDays;
return(TmpDays);
end; 2.SQL Server视图转MYSQL视图,附上SQL Server视图如下,那位好心人帮我转一下:
错误提示: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 'ON PatrolLine.LineID = PatrolDay.Line_ID AND PatrolLine_Dot.Line' at line 16 SELECT PatrolDay.PatrolerNo, PatrolDay.EvtTime, PatrolDay.DueTime,
PatrolDay.FactTime, PatrolDay.ErrorMinute, PatrolDay.Flag,
PatrolDay.Work_ID, PatrolDay.ID, PatrolLine.LineName,
PatrolType.TypeName, PatrolLine_Dot.LineDotID, PatrolLine.LineID,
USERS.UserName, DOOR.DoorName, PatrolDot.DcuID,
PatrolDot.DoorAddr, USERS.Dept_ID AS DeptID, DEPT.DeptName
FROM PatrolLine_Dot INNER JOIN
PatrolLine ON
PatrolLine_Dot.Line_id = PatrolLine.LineID LEFT OUTER JOIN
PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND
PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr RIGHT OUTER JOIN
PatrolType INNER JOIN
USERS INNER JOIN
PatrolDay ON USERS.UserNo = PatrolDay.PatrolerNo ON
PatrolType.ID = PatrolDay.Flag INNER JOIN
DEPT ON USERS.Dept_ID = DEPT.ID ON
PatrolLine.LineID = PatrolDay.Line_ID AND
PatrolLine_Dot.LineDotID = PatrolDay.LineDot_ID LEFT OUTER JOIN
DOOR ON PatrolDot.DcuID = DOOR.DcuID AND
PatrolDot.DoorAddr = DOOR.DoorAddr 3.触发器--错误提示-->"Not allowed to set autocommit from a stored function or trigger"
CREATE DEFINER='root'@`localhost` TRIGGER `PatrolLineInsertA` AFTER INSERT ON `PatrolLine`
FOR EACH ROW
BEGIN
SET AUTOCOMMIT = 0;
START TRANSACTION;
if not Exists(Select LineGroupID From PatrolLineGroup Where LineGroupID = NEW.LineGroup_ID) THEN
rollback work;
else
commit;
END IF;
END;
错误提示: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 'return(TmpDays); end' at line 1 CREATE Function GetSatOrSunDay
(Types int,
SDates varchar(10),
Edates varchar(10))
Returns int
begin
declare TmpDays int;
select (case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end +
IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0,
datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) as tempfield
FROM tempdata INTO TmpDays;
return(TmpDays);
end; 2.SQL Server视图转MYSQL视图,附上SQL Server视图如下,那位好心人帮我转一下:
错误提示: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 'ON PatrolLine.LineID = PatrolDay.Line_ID AND PatrolLine_Dot.Line' at line 16 SELECT PatrolDay.PatrolerNo, PatrolDay.EvtTime, PatrolDay.DueTime,
PatrolDay.FactTime, PatrolDay.ErrorMinute, PatrolDay.Flag,
PatrolDay.Work_ID, PatrolDay.ID, PatrolLine.LineName,
PatrolType.TypeName, PatrolLine_Dot.LineDotID, PatrolLine.LineID,
USERS.UserName, DOOR.DoorName, PatrolDot.DcuID,
PatrolDot.DoorAddr, USERS.Dept_ID AS DeptID, DEPT.DeptName
FROM PatrolLine_Dot INNER JOIN
PatrolLine ON
PatrolLine_Dot.Line_id = PatrolLine.LineID LEFT OUTER JOIN
PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND
PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr RIGHT OUTER JOIN
PatrolType INNER JOIN
USERS INNER JOIN
PatrolDay ON USERS.UserNo = PatrolDay.PatrolerNo ON
PatrolType.ID = PatrolDay.Flag INNER JOIN
DEPT ON USERS.Dept_ID = DEPT.ID ON
PatrolLine.LineID = PatrolDay.Line_ID AND
PatrolLine_Dot.LineDotID = PatrolDay.LineDot_ID LEFT OUTER JOIN
DOOR ON PatrolDot.DcuID = DOOR.DcuID AND
PatrolDot.DoorAddr = DOOR.DoorAddr 3.触发器--错误提示-->"Not allowed to set autocommit from a stored function or trigger"
CREATE DEFINER='root'@`localhost` TRIGGER `PatrolLineInsertA` AFTER INSERT ON `PatrolLine`
FOR EACH ROW
BEGIN
SET AUTOCOMMIT = 0;
START TRANSACTION;
if not Exists(Select LineGroupID From PatrolLineGroup Where LineGroupID = NEW.LineGroup_ID) THEN
rollback work;
else
commit;
END IF;
END;
DELIMITER $$DROP FUNCTION IF EXISTS `test`.`GetSatOrSunDay`$$CREATE DEFINER=`root`@`localhost` FUNCTION `GetSatOrSunDay`(Types int,
SDates varchar(10),
Edates varchar(10)) RETURNS int(11)
begin
select (case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end +
IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0,
datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) as tempfield
INTO @TmpDays
FROM tempdata ;
return @TmpDays;
END$$DELIMITER ;
2
CREATE VIEW V_PatrolWork_Line_Dot
AS
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
FROM PatrolWork INNER JOIN PatrolLine_Dot ON PatrolWork.Line_ID = PatrolLine.LineID
INNER JOIN PatrolLine ON PatrolLine_Dot.Line_id = PatrolLine.LineID
INNER JOIN PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND
PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr
3
MYSQL似乎不支持在触发器中回滚
MYSQL版本号是:5.0.67-community-nt
SELECT PatrolWork.WorkID, PatrolLine.LineID, PatrolLine_Dot.DcuID,
PatrolLine_Dot.DoorAddr
FROM PatrolWork INNER JOIN PatrolLine_Dot ON PatrolWork.Line_ID = PatrolLine.LineID
INNER JOIN PatrolLine ON PatrolLine_Dot.Line_id = PatrolLine.LineID
INNER JOIN PatrolDot ON (PatrolLine_Dot.DcuID = PatrolDot.DcuID) AND
(PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr )
看看是否正确
错误提示:认不到FORM后的PatrolLine.LineID
Unknown column 'PatrolLine.LineID' in 'on clause'
检查表中是否有字段
(Types int,
SDates varchar(10),
Edates varchar(10))
Returns int
begin
declare TmpDays int;
select (case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end +
IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0,
datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) as tempfield
FROM tempdata INTO TmpDays;
return TmpDays;
end;
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 'return TmpDays;
end' at line 1
压缩上传www.access911.net/csdn
(Types int,
SDates varchar(10),
Edates varchar(10))
Returns int
begin
declare TmpDays int;
select (case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end +
IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0,
datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) INTO TmpDays #-- changed by ACMAIN
FROM tempdata ;
return TmpDays;
end; 老兄建议看一下mySQL的语法手册。 http://dev.mysql.com/doc/refman/5.1/zh/index.html
19.2.7.3. SELECT ... INTO Statement
SELECT col_name[,...] INTO var_name[,...] table_expr
我试了你上面贴的语法,还是通不过,至于你提到的语法,放在后面和前面没什么区别,不信你试下。
(Types int,
SDates varchar(10),
Edates varchar(10))
Returns int
begin
declare TmpDays int;
SET TmpDays=10;
return TmpDays;
end; 试试这个,缩小一下范围,
如果把IF单独拿出去执行也是正确的,就是放到Function中就有错误:
CREATE Function GetSatOrSunDay
(Types int,
SDates varchar(10),
Edates varchar(10))
Returns int
begin
declare TmpDays int;
select (case DAYOFWEEK('2009-02-28') When 7 then 1 else 0 end +
IF(((datediff('2009-08-31', '2008-02-22')/7) - (datediff('2009-08-31', '2008-02-22') div 7)) > 0,
datediff('2009-08-31', '2008-02-22') div 7 + 1, datediff('2009-08-31', '2008-02-22') div 7)) INTO TmpDays #-- changed by ACMAIN
FROM tempdata ;
return TmpDays;
end;
只要有问题的表,用WINRAR压缩
http://access911.net/csdn/FileDescription.asp?mdb=2009-3-4&id=15
2、VIEW太复杂,建议自己理一下关系
CREATE
/*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]*/
VIEW `dcu32`.`V_PatrolWork_Line_Dot`
AS
sELECT c.PatrolerNo, c.EvtTime, c.DueTime,
c.FactTime, c.ErrorMinute, c.Flag,
c.Work_ID, c.ID, PatrolLine.LineName,
PatrolType.TypeName, PatrolLine_Dot.LineDotID, PatrolLine.LineID,
d.UserName, DOOR.DoorName, PatrolDot.DcuID,
PatrolDot.DoorAddr, d.Dept_ID AS DeptID, DEPT.DeptName
FROM PatrolLine_Dot INNER JOIN PatrolLine ON
PatrolLine_Dot.Line_id = PatrolLine.LineID
inner join PatrolDay c on PatrolLine_Dot.LineDotID = c.LineDot_ID
LEFT OUTER JOIN PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND
PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr
RIGHT OUTER JOIN PatrolType ON PatrolType.ID = c.Flag
INNER JOIN USERS d on d.UserNo = c.PatrolerNo
INNER JOIN DEPT ON d.Dept_ID = DEPT.ID
LEFT OUTER JOIN DOOR ON PatrolDot.DcuID = DOOR.DcuID AND PatrolDot.DoorAddr = DOOR.DoorAddr
3、
DELIMITER $$DROP FUNCTION IF EXISTS `dcu32`.`GetSatOrSunDay`$$CREATE DEFINER=`root`@`localhost` FUNCTION `GetSatOrSunDay`(Types1 int,
SDates1 varchar(10),
Edates1 varchar(10)) RETURNS int(11)
begin
declare TmpDays int;
if types1 = 1 then
select (case DAYOFWEEK(Edates1) When 7 then 1 else 0 end
+ IF(((datediff(Edates1, SDates1)/7) - (datediff(Edates1, SDates1) div 7)) > 0,
datediff(Edates1, SDates1) div 7 + 1, datediff(Edates1, SDates1) div 7)) into TmpDays
from tempdata limit 1;
else
select (case DAYOFWEEK(SDates1) When 1 then 1 else 0 end
+ IF(((datediff(Edates1, SDates1)/7) - (datediff(Edates1, SDates1) div 7)) > 0,
datediff(Edates1, SDates1) div 7 + 1, datediff(Edates1, SDates1) div 7)) into TmpDays
from tempdata limit 1;
end if;
return(TmpDays);
end$$DELIMITER ;均在SQLYOG下测试没有问题
TO:WWWWA
2.视图(V_PatrolDay)<>V_PatrolWork_Line_Dot请看需改写视图,如下附上
3.触发器-->没解决.没有相关资料表明MYSQL支持表的回滚.CREATE VIEW V_PatrolDay
AS
SELECT PatrolDay.PatrolerNo, PatrolDay.EvtTime, PatrolDay.DueTime,
PatrolDay.FactTime, PatrolDay.ErrorMinute, PatrolDay.Flag,
PatrolDay.Work_ID, PatrolDay.ID, PatrolLine.LineName,
PatrolType.TypeName, PatrolLine_Dot.LineDotID, PatrolLine.LineID,
USERS.UserName, DOOR.DoorName, PatrolDot.DcuID,
PatrolDot.DoorAddr, USERS.Dept_ID AS DeptID, DEPT.DeptName
FROM PatrolLine_Dot INNER JOIN
PatrolLine ON
PatrolLine_Dot.Line_id = PatrolLine.LineID LEFT OUTER JOIN
PatrolDot ON PatrolLine_Dot.DcuID = PatrolDot.DcuID AND
PatrolLine_Dot.DoorAddr = PatrolDot.DoorAddr RIGHT OUTER JOIN
PatrolType INNER JOIN
USERS INNER JOIN
PatrolDay ON USERS.UserNo = PatrolDay.PatrolerNo ON
PatrolType.ID = PatrolDay.Flag INNER JOIN
DEPT ON USERS.Dept_ID = DEPT.ID ON
PatrolLine.LineID = PatrolDay.Line_ID AND
PatrolLine_Dot.LineDotID = PatrolDay.LineDot_ID LEFT OUTER JOIN
DOOR ON PatrolDot.DcuID = DOOR.DcuID AND
PatrolDot.DoorAddr = DOOR.DoorAddr
2、主要你要理一下连接关系,比较复杂