>In a table called "Steps", I have the following fields:
>
>ID, B4Me, Dsc,
>
>-ID field contains a unique ID for each record
>-B4Me contains the ID of some other record in the table that MUST appear in a
>result set, BEFORE this record. B4Me may be null. This is called the "B4Me"
>order.
>-Records will be entered in random order, so Natural order cannot be relied
>upon as a substitute for B4Me
>
>I need an SQL statement that will return all records with a user supplied
>search word (such as "oil", or "glycol" or "micron") in the Dsc field, which
>could return
>a variable number of records, but what ever the number of records returned,
>the *order* must be such that each record is preceded by the record who's ID
>matches the B4Me value of a given record.&nbs p;
>
>Important Notes:
>
>- The SQL may return one or more records with a blank B4Me. In which case,
>Natural order can be used for those records, and they are all considered
>"First" or
>rather, "Before everything else". Once all the records with a blank B4Me are
>listed, the remaining must be in the B4Me order.
>
>- It is possible, though unlikely, that following the B4Me order will result
>in a circular reference. (Two or more records that reference each other, or
>the last
>record in a large result set, references the first (or any other in the result
>set) record in the B4Me field). In these cases, the SQL must either list all
>records until
>a circular reference is detected, then don't list the "offending" record, or
>display an error message, but it must not hang FB and get an "out of memory"
>error.
>
>Thanks in advance for any help you can provide.
The possibility of a circular reference, makes this a difficult task. SQL is
great for working with sets of data, iteration and recursion are more
difficult, but doable with CTEs or stored procedures, but I cannot think of how
to exit a circular reference "gracefully" within one statement.
Hence, I'd recommend using a global temporary table:
CREATE GLOBAL TEMPORARY TABLE MyTmp
(ID INTEGER, OrderNo INTEGER)
ON COMMIT DELETE ROWS; --doesn't really matter whether you commit or delete
rows in this particular case...
and then EXECUTE BLOCK or a STORED PROCEDURE;
EXECUTE BLOCK(MySearch varchar(50) = :MySearch) Returns(OutID Integer) as
declare variable ID integer;
declare variable ID2 integer;
declare variable OrderNo integer;
begin
delete from MyTmp; --start afresh every time you execute this block
OrderNo = 1000;
for select ID, B4Me
from "Steps"
where Dsc containing :MySearch
into :ID, :ID2 do
begin
while (:ID is not null) do
begin
if exists(select * from MyTmp where ID = :ID) then
begin --This is how to escape from a circular reference
id = null;
end
else
begin
INSERT INTO MyTmp(ID, OrderNo)
VALUES(:ID, :OrderNo);
OrderNo = OrderNo - 1;
ID = ID2;
if (ID is not null) then
begin
SELECT B4Me from "Steps" where ID = :ID into :ID2;
end
end
end
end
for select ID from MyTmp order by OrderNo into :OutID do
suspend;
end
I've interpreted "Before everything else" to mean "before any of its own
children", not "before any children regardless of heritage" - or, to put it
simple - you would appear before all your children, but not necessarily before
my children.
Hopefully, this can serve as a template for how you can solve your situation,
despite being more than one statement.
Set
