On Jun 19, 2011, at 5:12 PM, jose isaias cabrera wrote:
>
> "Nico Williams" wrote...
>
>> On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor <punk.k...@gmail.com>
>> 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>
>
Try to do the two queries separately and see which one croaks. Most likely it
is the second UPDATE query. You might need to enclose the sub-SELECTs (the ones
in the CASE and WHENs) in parens. From the docs
Scalar Subqueries
A SELECT statement enclosed in parentheses may appear as a
scalar quantity. A SELECT used as a scalar quantity must
return a result set with a single column. The result of the
expression is the value of the only column in the first row
returned by the SELECT statement. If the SELECT yields more
than one result row, all rows after the first are ignored.
If the SELECT yields no rows, then the value of the expression
is NULL.
All types of SELECT statement, including aggregate and
compound SELECT queries (queries with keywords like UNION
or EXCEPT) are allowed as scalar subqueries.
[http://www.sqlite.org/lang_expr.html]
> 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
