--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 08:20 AM 3/10/2005 +0000, you wrote:
> >I need to do a search on a table in a Firebird db, looking for the
> >occurence of two words in a particular piece of text. The words

are

> >indexed in the table against chapter, section, paragraph and line.
> >I've produced an SQL string:
> >
> >SELECT DISTINCT ALLENENC.WORD, ALLENENC.CHAPTER, ALLENENC.SECTION
> >FROM ALLENENC INNER JOIN ALLENENC AS ALLENENC_1 ON

(ALLENENC_1.LINE

> >= ALLENENC.LINE) AND (ALLENENC.PARAGRAPH = ALLENENC_1.PARAGRAPH)

AND

> >(ALLENENC.CHAPTER = ALLENENC_1.CHAPTER) AND (ALLENENC.SECTION =
> >ALLENENC_1.SECTION) WHERE (((ALLENENC.WORD)='PAIN') AND
> >((ALLENENC_1.WORD)='LEFT'));
> >
> >where the table is called ALLENENC. The WORD column is indexed.
> >This sql works in other db's I've tried but in Firebird (using
> >dotNet), I get the error(s)
> >
> >No message for error code 335544569 found.
> >No message for error code 335544436 found.
> >No message for error code 335544634 found.
> >No message for error code 335544382 found.
>
> All of these are genuine error codes, all relating to an unknow

token

> (keyword) in your DSQL statement.
>
>
> >I can open one table and search on one word but it won't let me

link

> >in a second instance of the table.
>
> Sure you can, but make sure that *both* hits on the table are

aliased; and

> I think you will find that the keyword 'AS' is the one causing

the 'unknown

> token' error. Write your query along these lines:
>
> SELECT DISTINCT
> a1.WORD, a1.CHAPTER, a1.SECTION
> FROM ALLENENC a1
> JOIN ALLENENC a2
> ON a2.LINE = a1.LINE
> AND a1.PARAGRAPH = a2.PARAGRAPH
> AND a1.CHAPTER = a2.CHAPTER
> AND a1.SECTION = a2.SECTION
>
> WHERE a1.WORD='PAIN'
> AND a2.WORD='LEFT';
>
> ./heLen

Thanks Helen - that worked fine. Problem now is getting an
intersection between 24000 hits and 33000 hits takes ages. But
that's a data issue.

Neil