Re: [firebird-support] SQL puzzle: Order based on Prerequisite
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
Re: [firebird-support] SQL puzzle: Order based on Prerequisite
Hi, or better for “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” select CASE WHEN B4ME IS NULL THEN 0 ELSE 1 END, CASE WHEN B4ME IS NULL THEN ID ELSE B4ME+1 END, other_fields FROM STEPS ORDER BY 1 ASC, 2 ASC regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Saturday, April 11, 2015 10:03 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] SQL puzzle: Order based on Prerequisite Hi, you must rethink what you try to retrive. What is then sense of returning somethink in case of cycles? If you have cycles then you do not get something before.. But if you need something strange then it is simple ID as integer select CASE WHEN B4ME IS NULL THEN ID ELSE B4ME+1 END, other_fields FROM STEPS ORDER BY 1 ASC but with cycles i will prefere recursive cte or stored proc and make possibility of duplicates in resultset regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Wednesday, April 08, 2015 4:28 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] SQL puzzle: Order based on Prerequisite 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.nb sp; 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./ p Thanks in advance for any help you can provide.
Re: [firebird-support] SQL puzzle: Order based on Prerequisite
Hi, you must rethink what you try to retrive. What is then sense of returning somethink in case of cycles? If you have cycles then you do not get something before.. But if you need something strange then it is simple ID as integer select CASE WHEN B4ME IS NULL THEN ID ELSE B4ME+1 END, other_fields FROM STEPS ORDER BY 1 ASC but with cycles i will prefere recursive cte or stored proc and make possibility of duplicates in resultset regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Wednesday, April 08, 2015 4:28 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] SQL puzzle: Order based on Prerequisite 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.nb sp; 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.
[firebird-support] SQL puzzle: Order based on Prerequisite
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. 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.
