"Nico Williams" wrote...
> On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor <[email protected]>
> wrote:
>> 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.
>
> Rewrite the statements as:
>
> INSERT ... WHERE ... AND <some condition>;
>
> Similarly for SELECT, UPDATE, and DELETE.
>
I have rewritten 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-19 18:02:16'
WHERE subProjID = 9144 AND lang = 'ES-LA'
AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job';
UPDATE LSOpenJobs SET ProjFund =
CASE PSubClass
WHEN 'Portal-Fee' THEN
CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs
WHERE subProjID = 9144 AND lang = 'ES-LA'
AND PSubClass != 'Portal-Fee'
WHEN < 5000 THEN
SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs
WHERE subProjID = 9144 AND lang = 'ES-LA'
AND PSubClass != 'Portal-Fee'
WHEN BETWEEN 5000 AND 20000 THEN
SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs
WHERE subProjID = 9144 AND lang = 'ES-LA'
AND PSubClass != 'Portal-Fee'
ELSE
0.0
END
END;
COMMIT TRANSACTION;
This is what I get when I run that code:
sqlite>
sqlite> BEGIN IMMEDIATE TRANSACTION;
sqlite>
sqlite> UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) *
.10,2) FR
OM 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-19 18:02:16'
...> WHERE subProjID = 9144 AND lang = 'ES-LA'
...> AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job';
sqlite>
sqlite> UPDATE LSOpenJobs SET ProjFund =
...> CASE PSubClass
...> WHEN 'Portal-Fee' THEN
...> CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs
...> WHERE subProjID = 9144 AND lang = 'ES-LA'
...> AND PSubClass != 'Portal-Fee'
...> WHEN < 5000 THEN
...> SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs
...> WHERE subProjID = 9144 AND lang = 'ES-LA'
...> AND PSubClass != 'Portal-Fee'
...> WHEN BETWEEN 5000 AND 20000 THEN
...> SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs
...> WHERE subProjID = 9144 AND lang = 'ES-LA'
...> AND PSubClass != 'Portal-Fee'
...> ELSE
...> 0.0
...> END
...> END;
Error: near "SELECT": syntax error
sqlite> COMMIT TRANSACTION;
sqlite>
I am trying to hit two things in one shot. Here is the idea, if a project
has a Portal-Fee charge, then if the total of Xtra8 is < 5000, the ProjFund
portal fee is 1.75% of the ProjFund, if >= 5000, but less then 20000, then
1.25%, otherwise Portal-Fee is 0.
I know how to do it programmatically, but, I would like to do it all within
the SQL call. If possible...
Thanks,
jose
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
