Helen -- Wow, thanks for all the explanation. I'm still confused
about a couple of things if you have any energy left for me.
> FTS doesn't (can't!!) alter the underlying SQL language of the
> database.
That was what I assumed.
> An FTS search uses the indexes on whatever search metadata you have
defined
> to find the primary keys of rows that satisfy a search criterion on
> specified columns. ___So you would use the found primary key (or
use it to
> find the required foreign key) in order to construct a set of join
criteria._____
That's exactly what I want to do. (I.e., use found primary key set as
a join criteria with rest of query results.) What's still opaque to
me is how I get that set of primary key values to join with the
result set of the non-FTS part of my query.
> However, a highly valid approach in Firebird/IB is to define such
outputs
> in selectable stored procedures. By this means, you can finely
predefine
> an output set without the need for temporary tables. Such output
sets can
> be requested directly or joined, as an intermediate set, to tables,
views
> and other selectable SPs.
>
> So, taking your DBISAM example:
>
> You would move the search part of the query into a FOR...SELECT
structure
> in a SP that has your required inputs and outputs defined as
> arguments.
Let's say you name this SP TEXTSEARCH and it's declared like this:
>
> create procedure TEXTSEARCH (from_date timestamp, to_date
timestamp,
> search_argument varchar(99))
> returns (
> pkvalue integer,
> retvar1 sometype,
> retvar2 sometype,
> ....etc.
> )
> as
> begin
> FOR SELECT.....DO
> begin
> ...
> suspend;
> end
> end
>
> You can use the returned pkvalue to join the output of a selectable
SP to a
> table, view, etc.
At least part of that makes sense to me. The main thing I'm confused
about is how/whether the set of primary keys that satisfy the full-
text-search-criteria is going to be available for me to join with?
Or is the full-text-search set somehow tested for in the begin..end
structure of the selectable query?. And if so, how?
Basically, I'm not sure whether you're suggesting one of two things:
(1) Break my original query into two parts. Send the 'Select * from
Table where date1 between '2002-10-2' and 2003-4-1' part to a
selectable procedure. Then join the results of that selectable
procedure to something else that has the primary keys satisfying the
full-text-search: Select * from selproc('2002-10-2', '2003-4-1'
inner join <FTS PK set> on selproc.pk = FTSResults.pk'. That sounds
awfully nifty, but where would I get the result set of the FTS query
to join to the result set from the other part of the original query?
OR
(2) Nest the FTS PK check beneath the non-FTS part of your original
Select by making a selectable procedure. Have the non-FTS criteria
as arguments of the selectable procedure. Test for satisfaction of
the FTS criteria (somehow!) within the DO clause of the selectable
procedure.
I really appreciate your clarification on this. Whether I can easily
handle conjoining FTS criteria and non-FTS-criteria in Firebird will
basically determine whether it's usable for me or not.