At 03:58 AM 15/04/2005 +0000, you wrote:

>I have three tables - REGIONS, AGENTS, MANAGERS.
> >From a list of queryTERMS (comma separated list), I want to be able
>to retrieve matches for REGIONS, AGENTS or MANAGERS. The heirarchy
>is Managers-->Agents-->Regions.
>A foreign key in REGIONS relates REGIONS to the Primary Key in
>AGENTS. A foreign key in AGENTS relates AGENTS to the Primary Key in
>MANAGERS.
>Thanks in advance for the help!

What you supplied is nothing like what I would call "simplified
pseudocode". It looks awfully like a whole M$ stored procedure, actually...

As a clue, you don't need temp tables to do this at all, since you can
process this input string in a loop, directly, and fetch the values that
you want in your application as a selectable set.

Write two stored procedures.

-- The first will be an executable SP that returns one 'term' from the top
of a string supplied as input and the balance of the string, beheaded.

-- The second will be a selectable SP that takes your input string and
outputs a set of tuples. In your application, you simply select from the
stored procedure and it will return this set as though it were the output
of a select on tables.

The tuple is defined in the RETURNS clause of the selectable SP.

Inside this SP, execution iterates through the input string in a WHILE
loop, calling the first SP to return the first term and the beheaded
string, ready for the next iteration...until the string is empty. (In here
you'll use the internal function SUBSTRING() and, if trimming is wanted, a
couple of UDFs.)

As each execution of SP 1 returns a term, this becomes the variable that is
used to query the other tables, selecting the required values into the
corresponding values in the output tuple. As each output tuple is filled,
it is passed to the output buffer using a SUSPEND; statement and the tuple
is reinitialised, ready to be refilled by the next query.

When you have finished with the current 'term', loop back and get the next
'term' with your next call to SP 1.

You don't need a counter to drive the WHILE loop. You don't even need to
count the terms at all. You just need to break out of the loop (with
LEAVE) when the result of beheading your input string is finally an empty
string or null (I test for both).

Another tip: in Firebird, never mix implicit and explicit join syntax. An
all-inner-join query can be either implicit or explicit, but not both. If
there are any outer joins (LEFT, RIGHT, FULL), use ONLY explicit joins.

If you haven't studied Firebird's procedure language yet, now would be a
really good time to do so. It's not at all like MSSQL's procedural
"language". It's a compact structured programming language with its own
syntax rules. If you don't have The Firebird Book, you can find the basics
in the IB 6 manuals (Data Definition Guide and Language Reference), with
Firebird-specific extensions in the Firebird 1 and 1.5 main release notes.

hth to get you under way.

./hb