On Jun 17, 2011, at 4:56 PM, jose isaias cabrera wrote:
> 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'
The above is not SQL. You can't have a SQL statement begin with CASE. SQL
statements can only begin with either SELECT or UPDATE or CREATE or DELETE or
ALTER, etc. CASE is an expression, and has to be a replacement for a column. I
can't even begin to help you rewrite your statement, but what you are trying to
accomplish is something like --
if (some condition)
UPDATE this
else
UPDATE that
Either accomplish the above in a programming language, or rewrite it as
separate queries, or hope Igor or someone can help you rewrite the above into a
single query.
Puneet.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
