steve mtangoo wrote:
> select * from Bible where (book> :1 or (book=:1 and (chapter>:1 or
> (chapter=:1 and verse>=:1) and (book< :3 or (book=:3 and (chapter<:1 or
> (chapter=:1 and verse<=:1);
> Does not work. Is there any issue
Yes, don't use :1. Just use the number 1.
SELECT *
FROM Bible
WHERE (
book > 1 OR ... and so on
)
>
> On Mon, Dec 13, 2010 at 6:11 PM, Puneet Kishor<punk.k...@gmail.com> wrote:
>
>>
>> steve mtangoo wrote:
>>> Hi Igor,
>>> would you explain what are bookLow and chapterHigh?
>>> Do you mean lowest book and highest chapter?
>> :bookLow and :chapterHigh are place holders for numbers you will
>> provide. Those numbers stand for the lowest numbered book and the
>> highest numbered chapter between which you want to search.
>>
>>
>>> I want to test this query but please help me to understand it.
>>> select * from Bible where
>>> (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or
>>> (chapter=:chapterLow and verse>=:verseLow)
>>> and
>>> (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or
>>> (chapter=:chapterHigh and verse<=:verseHigh);
>>>
>>> And thanks to everybody who is helping here!
>>>
>>> On Mon, Dec 13, 2010 at 5:13 PM, Igor Tandetnik<itandet...@mvps.org>
>> wrote:
>>>> steve mtangoo<mwinjili...@gmail.com> wrote:
>>>>> I have a script that is supposed to query the Bible scriptures between
>>>> two
>>>>> intervals. My table is named Bible and have columns: ID (int), Book
>>>> (int),
>>>>> Chapter(int), Verse (int) and Scripture(text).
>>>>>
>>>>> Now the books are unique i.e. 1-66 but chapters keep repeating, and so
>> do
>>>>> verses. Now suppose I want to get scripture between book 1 chapter 1
>>>> verse 1
>>>>> and book 2 chapter 3 Verse 1, how do I go about? My knowledge of
>> SQLite3
>>>>> have taken me to a dead end!
>>>> Here's a straighforward answer:
>>>>
>>>> select * from Bible where
>>>> (book> :bookLow or (book=:bookLow and (chapter>:chapterLow or
>>>> (chapter=:chapterLow and verse>=:verseLow)
>>>> and
>>>> (book< :bookHigh or (book=:bookHigh and (chapter<:chapterHigh or
>>>> (chapter=:chapterHigh and verse<=:verseHigh);
>>>>
>>>> Here's a trick to make it shorter:
>>>>
>>>> select * from Bible where
>>>> book*1000000 + chapter*1000 + verse between
>>>> :bookLow*1000000 + :chapterLow*1000 + :verseLow
>>>> and
>>>> :bookHigh*1000000 + :chapterHigh*1000 + :verseHigh;
>>>>
>>>> Basically, the idea is to assign each verse a unique number (the query
>>>> above assumes there are no more than 1000 verses per chapter and
>> chapters
>>>> per book; adjust multipliers if there are more). You may want to
>> consider
>>>> working with such sequential numbers internally in your program,
>> converting
>>>> between them and (book, chapter, verse) format on input/output only. So,
>>>> define your table as Bible(VerseNumber integer primary key, Scripture
>> text),
>>>> and the query becomes simply
>>>>
>>>> select * from Bible where VerseNumber between :VerseLow and :VerseHigh;
>>>>
>>>> --
>>>> 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
>>
>>
>> --
>> Puneet Kishor http://punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Fellow
>> http://creativecommons.org/about/people/fellows#puneetkishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---------------------------------------------------------------------------
>> Assertions are politics; backing up assertions with evidence is science
>> ===========================================================================
>> _______________________________________________
>> 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
--
Puneet Kishor http://punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---------------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
===========================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
