"Mr. Puneet Kishor" wrote... > > On Jun 19, 2011, at 10:04 PM, jose isaias cabrera wrote: > >> "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... >> > > > Given your query, I don't think there is a way around. There are only two > ways CASE.. WHEN.. THEN works, and both are documented. The documentation, > as you yourself noted, says very clearly that, "The only difference > between the .. 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" >
Thanks, Puneet. With the such a powerful tool SQL system, I would have expected the WHEN to allow an expression. Perhaps this can be thought of in the future. :-) I guess I will go back to your very original suggestion, to do it within the programming language. > 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. thanks, josé _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
