subject:"\[sqlite\] Help with CASE WHEN"

Re: [sqlite] Help with CASE WHEN

2011-06-19 Thread jose isaias cabrera


"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 
> 
> 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 ;
>
> 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 2 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 2 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')
>>   ...>

Re: [sqlite] Help with CASE WHEN

2011-06-19 Thread Mr. Puneet Kishor


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 
 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 ;
 
 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 2 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 2 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'
>   ...>

Re: [sqlite] Help with CASE WHEN

2011-06-19 Thread jose isaias cabrera

"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 
>>> 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 ;
>>>
>>> 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 2 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 2 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 2 THEN
   ...> (SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs
   ...>

Re: [sqlite] Help with CASE WHEN

2011-06-19 Thread Mr. Puneet Kishor


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  
>> 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 ;
>> 
>> 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 2 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 2 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

Re: [sqlite] Help with CASE WHEN

2011-06-19 Thread jose isaias cabrera


"Nico Williams" wrote...

> On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor  
> 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 ;
>
> 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 2 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 2 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 2, 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

Re: [sqlite] Help with CASE WHEN

2011-06-17 Thread Nico Williams

On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor  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 ;

Similarly for SELECT, UPDATE, and DELETE.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with CASE WHEN

2011-06-17 Thread Mr. Puneet Kishor


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 2 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') >= 2),
>  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') >= 2),
>  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

[sqlite] Help with CASE WHEN

2011-06-17 Thread jose isaias cabrera


Greetings.

I have used CASE before, but for simple codes and it works.  However, I am 
trying to break down some calculations and it-s not working.  I am getting,

   ...>
   ...> END;
Error: near "CASE": syntax error
sqlite> COMMIT TRANSACTION;
Error: cannot commit - no transaction is active
sqlite>

This is 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-17 17:40:05'
WHERE subProjID = 9144 AND lang = 'ES-LA'
AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job';

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 2 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') >= 2),
  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') >= 2),
  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'

END;
COMMIT TRANSACTION;

Any help to clean this up and getting the error out would be great.

thanks,

josé

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with CASE WHEN

Re: [sqlite] Help with CASE WHEN

Re: [sqlite] Help with CASE WHEN

Re: [sqlite] Help with CASE WHEN

Re: [sqlite] Help with CASE WHEN

Re: [sqlite] Help with CASE WHEN

Re: [sqlite] Help with CASE WHEN

[sqlite] Help with CASE WHEN

8 matches

Site Navigation

Mail list logo

Footer information