Greetings. I have used CASE before, but for simple codes and it works. However, I am trying to break down some calculations and it-s not working. I am getting,
...> ...> END; Error: near "CASE": syntax error sqlite> COMMIT TRANSACTION; Error: cannot commit - no transaction is active sqlite> This is the code... BEGIN IMMEDIATE TRANSACTION; UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .10,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'), Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'PM' AND PSubClass != 'Portal-Fee' AND PSubClass != 'Rush-Job'), Xtra6 = '10% of total', XtraB = '2011-06-17 17:40:05' WHERE subProjID = 9144 AND lang = 'ES-LA' AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job'; CASE (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') WHEN < 5000 THEN UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') <= 5000), Xtra8 = (SELECT round(sum(Xtra8) * .0175,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') <= 5000), Xtra6 = '1.75% of total', XtraB = '2011-06-17 17:40:05', XtraD = '1.75%' WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass = 'Portal-Fee' WHEN BETWEEN 5000 AND 20000 THEN UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') > 5000), Xtra8 = (SELECT round(sum(Xtra8) * .0125,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') > 5000), Xtra6 = '1.25% of total', XtraB = '2011-06-17 17:40:05', XtraD = '1.25%' WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass = 'Portal-Fee' ELSE UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') >= 20000), Xtra8 = (SELECT round(sum(Xtra8) * .0,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') >= 20000), Xtra6 = 'No fee charged', XtraB = '2011-06-17 17:40:05', XtraD = '0.00%' WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass = 'Portal-Fee' END; COMMIT TRANSACTION; Any help to clean this up and getting the error out would be great. thanks, josé _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users