Haskell Sqlite 3 CASE语句语法错误 [英] Haskell Sqlite 3 CASE statement syntax error

问题描述

这是HDBC和Sqlite3。它说CASE附近有一个语法错误。但我可以找到这样的。有没有？

calculateNoOfStocksTraded :: String-> Database.HDBC.Sqlite3.Connection-> IO（）
calculateNoOfStocksTraded代码conn = do

  run connCREATE TRIGGER calcStocks \ 
 \ AFTER INSERT ON historicalData \ 
 \ FOR EACH ROW \\ \\ 
 \ BEGIN \ 
 \ CASE WHEN（SELECT COUNT（*）FROM historicalData）> = 1 THEN \ 
 \ UPDATE company \ 
 \ SET noOfStocks =（SELECT SUM（volume）FROM historicalData WHERE companyCode =？）\ 
 \ WHERE code =？; \ 
 \ ELSE \ 
 \ UPDATE company \ 
 \ SET noOfStocks = 0 \ 
 \ WHERE code =？ ; \ 
 \ END \ 
 
 \ END; [toSql code，toSql code，toSql code] 
 
 commit conn 
  

解决方案

CREATE TRIGGER 期望触发器的主体是 update-stmt ， insert-stmt ， delete -stmt 或 select -stmt ;所以我们需要按照其中一种方式重写触发器; p>

事实证明，您可以简化触发器，以便我们可以用任何东西来替换 CASE 语法;如果 count（foo。*）<1 ，那么它必定是 sum（foo.bar）= 0 ，因此：

在sqlite中， count（foo。*）<1 ，导致 sum（foo.bar）IS NULL 这是不幸的;但我们可以通过将 null 转换成 0 与 coalesce（）：

pre $ code CREATE TRIGGER calcStocks
AFTER INSERT ON historicalData
FOR EACH ROW
BEGIN
UPDATE company
SET noOfStocks =（SELECT COALESCE（SUM（volume），0）FROM historicalData WHERE companyCode =？ ）
WHERE code =？ ;
END

This is HDBC and Sqlite3. It says there's a syntax error near "CASE". But I can'd find such. Is there any ?

calculateNoOfStocksTraded::String->Database.HDBC.Sqlite3.Connection->IO () calculateNoOfStocksTraded code conn=do

                                run conn " CREATE TRIGGER calcStocks \
                                           \ AFTER INSERT ON historicalData \
                                           \ FOR EACH ROW \
                                           \ BEGIN \
                                           \ CASE WHEN (SELECT COUNT(*) FROM historicalData) >= 1  THEN \
                                           \    UPDATE company \
                                           \    SET noOfStocks=(SELECT SUM(volume) FROM historicalData  WHERE companyCode= ? ) \
                                           \    WHERE code= ? ; \
                                           \ ELSE \
                                           \    UPDATE company \
                                           \    SET noOfStocks=0  \
                                           \    WHERE code= ? ; \
                                           \ END \

                                           \ END; " [toSql code,toSql code, toSql code]

                                commit conn

解决方案

CREATE TRIGGER expects as the body of the trigger to be one of update-stmt, insert-stmt, delete-stmt or select-stmt; So we need to rewrite the trigger in terms of one of those things;

As it turns out, you can simplify your trigger such that we can replace the CASE syntax with nothing; if count(foo.*) < 1, then it must be the case that sum(foo.bar) = 0 and thus:

In sqlite; count(foo.*) < 1, causes sum(foo.bar) IS NULL which is unfortunate; but we can make it do the right thing by turning the null into a 0 with coalesce():

CREATE TRIGGER calcStocks
AFTER INSERT ON historicalData
FOR EACH ROW
BEGIN
   UPDATE company
   SET noOfStocks=(SELECT COALESCE(SUM(volume), 0) FROM historicalData  WHERE companyCode= ? )
   WHERE code= ? ;
END

这篇关于Haskell Sqlite 3 CASE语句语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！