求sql语句case WHEN..then end问题。。(急)
我的语句
SELECT username, SUM(tichen) AS tichen, gongzi
FROM (SELECT login.username, login.gongzi,
CASE WHEN produit.tichen_type = 1 THEN sell.shulian * produit.tichen
WHEN produit.tichen_type = 0 THEN sell.shulian * sell.price * produit.tichen
END AS tichen
FROM sell INNER JOIN
produit ON sell.id_produit = produit.id INNER JOIN
login ON sell.id_login = login.id where sell.selldate >= '2007-10-01') a
GROUP BY username, gongzi
在sql 2000查询分析器测试完全通过并执行。
但是,我写在asp程序里的时候却是报错(操作符丢失),程序里用的是access数据库,难道access数据库不支持case WHEN..then end语法?郁闷中。。。。。
sql="SELECT username, SUM(tichen) AS tichen, gongzi "&_
"FROM (SELECT login.username, login.gongzi, "&_
"CASE "&_
"WHEN produit.tichen_type=1 THEN sell.shulian * produit.tichen "&_
"WHEN produit.tichen_type=0 THEN sell.shulian * sell.price * produit.tichen "&_
"END "&_
"AS tichen "&_
"FROM sell INNER JOIN "&_
"produit ON sell.id_produit = produit.id INNER JOIN "&_
"login ON sell.id_login = login.id where (sell.selldate>=#"&CDate(yy&"-"&mm&"-"&dd)&"# and sell.selldate<=#"&CDate(yy&"-"&mm&"-"&d)&"#)) a GROUP BY username,gongzi"
另外可以用access数据库文件实现我想要的结果吗?