把sql server语句改成access用的

[robot_zyt]

select a.*,employee.employeeName from
(
select tables.TablesID,tables.TablesName,
tables.TablesStatus,Tables.TablesDetail,
case
when tablesStatus<>'已就座' then tables.employeeID
else vDishTable.employeeID
end employeeID,
tables.position,vDishTable.DishTableID,
vDishTable.if_ok,
vDishTable.StartTime,vDishTable.DishTableDetail,
vDishTable.clientsNum
from tables
inner join employee on tables.Employeeid=Employee.EmployeeID
LEFT join (select DishTable.* from DishTable where DishTable.if_ok='否') vDishTable
on tables.TablesID=vDishTable.TablesID
where tables.RestaurantID=:RestaurantID
and tables.tablesID<>:OutTablesID
) a,employee
where a.employeeid=employee.employeeid
order by a.TablesID

现在数据库换成access了，原来的sql server上执行的，现在运行有错误，请问怎么改？

[WWWWA]

1、自己动手试试；
2、CASE WHEN->IIF
每一层连接加括号
3、有问题再问

[WWWWA]

1、自己动手试试；
2、CASE WHEN->IIF
每一层连接加括号
3、有问题再问

[ACMAIN_CHM]

select a.*,employee.employeeName 
from 
( 
select tables.TablesID,tables.TablesName,
     tables.TablesStatus,Tables.TablesDetail,
     case
     when tablesStatus <>'已就座' then tables.employeeID
     else vDishTable.employeeID
     end employeeID,
     tables.position,vDishTable.DishTableID,
    vDishTable.if_ok,
     vDishTable.StartTime,vDishTable.DishTableDetail,
     vDishTable.clientsNum
from ([tables] 
  inner join employee on tables.Employeeid=Employee.EmployeeID)
  LEFT join (select DishTable.* from DishTable where DishTable.if_ok='否') vDishTable 
  on [tables].TablesID=vDishTable.TablesID
where [tables].RestaurantID=:RestaurantID
  and [tables].tablesID <>:OutTablesID
) a,employee
where a.employeeid=employee.employeeid
order by a.TablesID

ACCESS中连续的 join 要加()

QQ群 48866293 / 12035577 / 7440532 / 13666209
ACCESS专业论坛
http://www.accessbbs.cn/bbs/index.php .
http://www.office-cn.net/vvb/ .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
.
http://www.office-cn.net/home/space.php?uid=141646 .

[robot_zyt]

我改成了
select a.*,employee.employeeName from
(
select tables.TablesID,tables.TablesName,
tables.TablesStatus,Tables.TablesDetail,
iif(tablesStatus<>'已就座',tables.employeeID, vDishTable.employeeID) as employeeID,
tables.position,vDishTable.DishTableID,
vDishTable.if_ok,
vDishTable.StartTime,vDishTable.DishTableDetail,
vDishTable.clientsNum
from [tables]
inner join employee on tables.Employeeid=Employee.EmployeeID
LEFT join (select DishTable.* from DishTable where DishTable.if_ok='否') vDishTable
on [tables].TablesID=vDishTable.TablesID
where[tables].RestaurantID=:RestaurantID
and [tables].tablesID<>:OutTablesID
) a,employee
where a.employeeid=employee.employeeid
order by a.TablesID

后还是报错，怎么回事？？

[WWWWA]

select a.*,employee.employeeName from
(
select tables.TablesID,tables.TablesName,
tables.TablesStatus,Tables.TablesDetail,
iif(tablesStatus <>'已就座',tables.employeeID, vDishTable.employeeID) as employeeID,
tables.position,vDishTable.DishTableID,
vDishTable.if_ok,
vDishTable.StartTime,vDishTable.DishTableDetail,
vDishTable.clientsNum
from ([tables]
inner join employee on tables.Employeeid=Employee.EmployeeID)
LEFT join (select DishTable.* from DishTable where DishTable.if_ok='否') vDishTable
on [tables].TablesID=vDishTable.TablesID
where[tables].RestaurantID=:RestaurantID
and [tables].tablesID <>:OutTablesID
) a,employee
where a.employeeid=employee.employeeid
order by a.TablesID

[robot_zyt]

select a.*,employee.employeeName from
(
select tables.TablesID,tables.TablesName,
tables.TablesStatus,Tables.TablesDetail,
iif(tablesStatus<>'已就座',tables.employeeID, vDishTable.employeeID) as employeeID,
tables.position,vDishTable.DishTableID,
vDishTable.if_ok,
vDishTable.StartTime,vDishTable.DishTableDetail,
vDishTable.clientsNum
from ([tables]
inner join employee on tables.Employeeid=Employee.EmployeeID)
LEFT join (select DishTable.* from DishTable where DishTable.if_ok='否') vDishTable
on [tables].TablesID=vDishTable.TablesID
where [tables].RestaurantID=:RestaurantID
and [tables].tablesID<>:OutTablesID
) a,employee
where a.employeeid=employee.employeeid
order by a.TablesID

现在是改成这样了，也还是不行，报‘未指定的错误’

[ACMAIN_CHM]

建议上传你的空MDB文件，只含这几表，不含数据。否则只能依靠人工来看，无法直接调试排错。

可以上传到下面任意BBS。

QQ群 48866293 / 12035577 / 7440532 / 13666209
ACCESS专业论坛
http://www.accessbbs.cn/bbs/index.php .
http://www.office-cn.net/vvb/ .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
.
http://www.office-cn.net/home/space.php?uid=141646 .

[WWWWA]

语法没有问题了，就是运行了，有可能要重新编制SQL语句

[robot_zyt]

按照ACMAIN_CHM的提示，我在http://www.accessbbs.cn/bbs/index.php发了一个帖，把带有表的数据库传上去了，你们帮我看一下，多谢了，我实在是搞不定了！

帖子联接是：
http://www.accessbbs.cn/bbs/viewthread.php?tid=19400&extra=page%3D1&frombbs=1

[ACMAIN_CHM]

where [tables].RestaurantID=:RestaurantID
and [tables].tablesID <>:OutTablesID

这两个 :RestaurantID, :OutTablesID 是做什么用的？参数查询？

我在你的MDB文件中建了个查询，内容为你的代码，运行时提示输入这两个参数。没有错误提示啊。

[robot_zyt]

我怎么就有问题，我用下面的代码(delphi2007)单独测试,
ADOQuery1.Parameters.ParamByName('RestaurantID').Value := '1';
ADOQuery1.Parameters.ParamByName('OutTablesID').Value := '003';
ADOQuery1.Prepared := true;
ADOQuery1.Open;

还是报‘未指定的错误’

[ACMAIN_CHM]

把参数名换一下试试

where [tables].RestaurantID=[vRestaurantID]
and [tables].tablesID <>[vOutTablesID]

问题可以确定不是ACCESS中SQL语句的问题，应该是在你的程序中如何使用参数的问题了。

[WWWWA]

adoquery1.Parameters.Clear;
adoquery1.Parameters.AddParameter;
adoquery1.Parameters[0].Value:='1';
adoquery1.Parameters[1].Value:='003';

[robot_zyt]

多谢ACMAIN_CHM的关注，我用access里的查询也是可以的，就是用delphi访问就不行，很有可能是delphi的ado中的bug,我再找找delphi的问题！

[robot_zyt]

终于找到原因了，tables.position中的position在access中为保留字段，不能用！