here is the view:
create view today_goal as
select 'monday' dow, monday_goal value from user_goals
union all
select 'tuesday' dow, tuesday_goal value from user_goals
union all
select 'wednesday' dow, wednesday_goal value from user_goals
union all
select 'thursday' dow, thursday_goal value from user_goals
union all
select 'friday' dow, friday_goal value from user_goals
union all
select 'saturday' dow, saturday_goal value from user_goals
union all
select 'sunday' dow, sunday_goal value from user_goals;
select dow, value from today_goal;
On Wed, May 11, 2011 at 8:51 PM, John <tauru...@gmail.com> wrote:
> ok, I will try. Meanwhile I created the actual view that does what I need.
> (considering I have many mondays, tuesdays... I replaced UNION with UNION
> ALL)
>
>
> On Wed, May 11, 2011 at 8:44 PM, Mr. Puneet Kishor <punk.k...@gmail.com>wrote:
>
>>
>> On May 11, 2011, at 7:37 PM, John wrote:
>>
>> > Igor,
>> > What you are suggesting will not work. You can only select values not
>> > columns using case.
>> >
>> >
>> > select case strftime('%w', 'now')
>> > when 0 then sunday_value
>> > when 1 then monday_value
>> > ...
>> > else saturday_value
>> > end
>> > from seven_days;
>> >
>> >
>>
>>
>> You must be new here. You have to understand the first rule of SQL. Igor
>> is never wrong. His query works just fine. See below --
>>
>> sqlite> CREATE TABLE seven_days (
>> ...> mon INTEGER,
>> ...> tue INTEGER,
>> ...> wed INTEGER
>> ...> );
>> sqlite> INSERT INTO seven_days VALUES (5, 3, 2);
>> sqlite> INSERT INTO seven_days VALUES (1, 4, 3);
>> sqlite> INSERT INTO seven_days VALUES (7, 8, 3);
>> sqlite> SELECT CASE Strftime('%w', 'now')
>> ...> WHEN 1 THEN mon
>> ...> WHEN 2 THEN tue
>> ...> ELSE wed
>> ...> END AS day_val
>> ...> FROM seven_days;
>> day_val
>>
>> ------------------------------------------------------------------------------
>> 2
>> 3
>> 3
>> sqlite>
>>
>>
>> >
>> >
>> > On Wed, May 11, 2011 at 8:30 PM, Igor Tandetnik <itandet...@mvps.org>
>> wrote:
>> >
>> >> On 5/11/2011 8:14 PM, John wrote:
>> >>> I am in situation where I need to keep as much logic as possible
>> within
>> >>> SQLite. However the query that I need to perform does not seem to be
>> >>> possible to perform.
>> >>>
>> >>> let's say I have a table with columns for each day of the week
>> >>>
>> >>> create table seven_days
>> >>> (monday_value integer,
>> >>> tueday_value integer,
>> >>> wednesday_value integer,
>> >>> ... );
>> >>>
>> >>> I want to select value from whatever day it is today. So if today is
>> >>> Tuesday, select from tuesday_value.
>> >>>
>> >>> Can I do it with pure SQlite?
>> >>
>> >> select case strftime('%w', 'now')
>> >> when 0 then sunday_value
>> >> when 1 then monday_value
>> >> ...
>> >> else saturday_value
>> >> end
>> >> from seven_days;
>> >>
>> >> --
>> >> Igor Tandetnik
>> >>
>> >> _______________________________________________
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > ~John
>> > _______________________________________________
>> > 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
>>
>
>
>
> --
> ~John
>
--
~John
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
