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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
