Thx, thats where i am headed -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Wednesday, April 05, 2017 9:53 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] recursive clause
Cem Dayanik (Ibtech-Software Infrastructure) wrote: > CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress > INTEGER) CREATE TABLE Instances (TypeId INTEGER, Address INTEGER) > CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count > INT, TotalSize INTEGER) > > What I need is, start with an address and add all reference addresses to the > list -with increasing level- unless it is already there. > > Not working with: > and refbyaddress not in (select address from allreferences) > > select * from ( > WITH RECURSIVE > allreferences(address, level) AS ( > values(435582892, 0) > union > SELECT refbyaddress, allreferences.level+1 FROM instancereferences, > allreferences > WHERE instancereferences.instanceaddress=allreferences.address and > allreferences.level < 1 and refbyaddress not in (select address from > allreferences) > ) > SELECT* FROM allreferences ) r, instances ins , types t where r.n = > ins.address and ins.typeid=t.id The documentation says (http://www.sqlite.org/lang_with.html#recursivecte): | The table named on the left-hand side of the AS keyword must appear | exactly once in the FROM clause of the right-most SELECT statement of | the compound select, and nowhere else. If you omit the level, the UNION automatically prevents loops. Do you really need the level? It might be possible to compute it in SQL afterwards, but that is likely to be inefficient. It might be a better idea to run Dijkstra's algorithm in your code. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Bu e-posta'nin içerdigi bilgiler (ekleri dahil olmak üzere) gizlidir. Onayimiz olmaksizin üçüncü kisilere açiklanamaz. Bu mesajin gönderilmek istendigi kisi degilseniz, lütfen mesaji sisteminizden derhal siliniz. IBTech A.S. bu mesajin içerdigi bilgilerin dogrulugu veya eksiksiz oldugu konusunda bir garanti vermemektedir. Bu nedenle bilgilerin ne sekilde olursa olsun içeriginden, iletilmesinden, alinmasindan, saklanmasindan sorumlu degildir. Bu mesajin içerigi yazarina ait olup, IBTech A.S.'nin görüslerini içermeyebilir. The information contained in this e-mail (including any attachments)is confidential. It must not be disclosed to any person without our authority. If you are not the intended recipient, please delete it from your system immediately. IBTech A.S. makes no warranty as to the accuracy or completeness of any information contained in this message and hereby excludes any liability of any kind for the information contained therein or for the information transmission, reception, storage or use of such in any way whatsoever. Any opinions expressed in this message are those of the author and may not necessarily reflect the opinions of IBTech A.S. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
