"Mr. Puneet Kishor" wrote... > > 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
Your-re right, the second one "croaks". When I use the parens, it appears to work, but, I now find a new error. 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 "<": syntax error Apparently, WHEN does not like < or >. It just wants one value. It also does not like BETWEEN... 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 BETWEEN 0 AND 4999.999999 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.00) ...> END ...> END; Error: near "BETWEEN": syntax error How does one uses WHEN, I tried reading the site, http://sqlite.org/lang_expr.html and this is the little bit that it has: The CASE expression A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages. The optional expression that occurs in between the CASE keyword and the first WHEN keyword is called the "base" expression. ... ... In a CASE with a base expression, the base expression is evaluated just once and the result is compared against the evaluation of each WHEN expression from left to right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression for which the comparison is true. Or, if none of the WHEN expressions evaluate to a value equal to the base expression, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions produce a result equal to the base expression, the overall result is NULL. ... ... The only difference between the following two CASE expressions is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END .... Does this means that each WHEN can only use a variable or value but not a expression? If not, then, how can I use an expression within WHEN. I am able to change the code to make it work to this, BEGIN IMMEDIATE TRANSACTION; UPDATE LSOpenJobs SET ProjFund = CASE PSubClass WHEN 'Portal-Fee' THEN CASE WHEN (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') < 5000 THEN (SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') WHEN (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') >= 5000 AND (SELECT round(sum(Xtra8),2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee') < 20000 THEN (SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass != 'Portal-Fee' ) ELSE (0.00) END END WHERE subProjID = 9144 AND lang = 'ES-LA' AND PSubClass = 'Portal-Fee'; COMMIT TRANSACTION; But, I don't want to recalculate the SELECT for each WHEN. I want to do it just once... thanks. josé _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
