On 8/18/08, Dennis Cote <[EMAIL PROTECTED]> wrote:
> P Kishor wrote:
>
> >
> > I would like to generate Snippets from MATCHes in two columns,
> > however, I get the following error: "unable to use function MATCH in
> > the requested context" with the following query --
> >
> > SELECT poem_id, context
> > FROM poems a JOIN (
> > SELECT
> > rowid,
> > Snippet(fts_poems, '<span class="hl">', '</span>',
> '…') AS context
> > FROM fts_poems
> > WHERE poem MATCH ? OR history MATCH ?
> > ) b ON a.poem_id = b.rowid
> >
> >
>
> Does this work for you?
>
> SELECT poem_id, context
> FROM poems as a
> JOIN (
> SELECT
> rowid,
> Snippet(fts_poems, '<span class="hl">', '</span>', '…') AS
> context
> FROM fts_poems
> WHERE rowid in
> (
> select rowid from fts_poems where poem MATCH ?
> union
> select rowid from fts_poems where history MATCH ?
> )
> ) as b ON a.poem_id = b.rowid;
>
> It runs each match in a separate subquery and doesn't generate an error
> when prepared by sqlite.
Sorry for the tardy response. Unfortunately, your suggestion does not
work. I believe, as pointed out in another email by Nicholas Brandon,
your suggestion is incorrect.
On the other hand, the following worked --
SELECT poem_id, context
FROM poems a JOIN (
SELECT
rowid,
Snippet(fts_poems, '<span class="hl">', '</span>',
'…') AS context
FROM fts_poems
WHERE fts_poems MATCH ?
) b ON a.poem_id = b.rowid
In other words, I have to match on the table name, which seems really
counter-intuitive. And, while it works for me in this instance,
because I have only two columns FTS-indexed, and I am trying to match
on those two columns, I can't imagine how the above would work if I
had, say, 3 columns indexed and wanted to search in only two of them.
I guess I would have to use the col:term kind of syntax.
FTS is really a brilliant addition to SQLite, and it really needs to
be documented more comprehensively and clearly, all the way from
compiling, setting up, indexing, and searching. The bits and pieces
are there, but they need to be brought together. I will try to do my
bit by improving the documentation and put it up there -- hopefully it
will be of help.
>
> HTH
> Dennis Cote
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
