Ya know, if you ever wanted to deviate from the SQL standard, a handy
upgrade to the CASE statement would permit split conditions on the case and
when statement, as in:
Case price1
when > 12 then 1
when > 30 then 2
else 0
end
dvn
On Thu, Mar 1, 2012 at 12:43 PM, Don V Nielsen <donvniel...@gmail.com>wrote:
> I think Simon's solution is in error, and Igor's is correct. In Simon's
> case, Slevel will be set to 1 if price1 is greater than 30. However, the
> original c function would set Slevel to 2 because price1 is greater than 12
> and it is greater than 30. Two increments of i are executed in that
> scenario. Igor's accumulating the boolean results gets you where you want
> to be; it's just a little cryptic.
>
> The original function was less than explicit. I'm assuming some kind of
> weighted value is being generated. My solution is probably the most wordy
> and least efficient performance wise, it is explicit in function. It also
> allows you to set the weight of each price1/price2 level.
>
> UPDATE bb SET Slevel =
> (
> (Case when price1>12 then 1
> when price1>20 then 2
> else 0
> End) +
> (Case when price2>20 then 1
> when price2>30 then 2
> when price2>80 then 3
> else 0
> End)
> );
>
>
> On Thu, Mar 1, 2012 at 7:21 AM, Igor Tandetnik <itandet...@mvps.org>wrote:
>
>> YAN HONG YE <yanhong...@mpsa.com> wrote:
>> > I have a sqlite database named bb:
>> >
>> >> Name Price1 Price2 Slevel
>> >> A1 23 231 NULL
>> >> A2 22 12 NULL
>> >> A3 21 223 NULL
>> >
>> > My question is:
>> > I want to update culumn Slevel by function myfunc():
>> >
>> > int myfunc():
>> > {int i=0;
>> > if (price1 >12)
>> > i++;
>> > if (price1>30)
>> > i++;
>> > if (price2>20)
>> > i++;
>> > if (price2>30)
>> > i++;
>> > if (price2>80)
>> > i++;
>> > return i;
>> > }
>>
>> Just run this query:
>>
>> update bb set Slevel = (price1>12) + (price1>30) + (price2>20) +
>> (price2>30) + (price2>80);
>>
>> --
>> Igor Tandetnik
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
