Help with CASE WHEN

classic Classic list List threaded Threaded
8 messages Options
jic
Reply | Threaded
Open this post in threaded view
|

Help with CASE WHEN

jic

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 20000 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') >= 20000),
      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') >= 20000),
      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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with CASE WHEN

Puneet Kishor-2

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 20000 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') >= 20000),
>      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') >= 20000),
>      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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with CASE WHEN

Nico Williams
On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor <[hidden email]> 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.

Nico
--
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: Help with CASE WHEN

jic

"Nico Williams" wrote...

> On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor <[hidden email]>
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with CASE WHEN

Puneet Kishor-2

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 <[hidden email]>
>> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: Help with CASE WHEN

jic
"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 <[hidden email]>
>>> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with CASE WHEN

Puneet Kishor-2

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 <[hidden email]>
>>>> 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.
>
> josé
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: Help with CASE WHEN

jic

"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
>>>>> <[hidden email]>
>>>>> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users