sql server - why cte recursive returns just the first row? -


please take @ cte , tell me what's wrong it? returns first row (anchor).

edit: 1 works @ should, wonder what's wrong tables , data!!!

create table menus (menuid uniqueidentifier primary key, menuname nvarchar(100)) create table menuitems (menuitemid uniqueidentifier primary key, pid uniqueidentifier null, menuid uniqueidentifier not null, menuitemname nvarchar(100) not null)  insert menus values ('3ab564a7-1d2c-4c43-91e3-a191016cbe67', 'main')  insert menuitems values ('d3b92d1b-7afa-4e11-af01-9f6a00b1b2a9', '00000000-0000-0000-0000-000000000000', '3ab564a7-1d2c-4c43-91e3-a191016cbe67', 'books') insert menuitems values ('70575c44-1ef8-497f-b7b8-a18300f645d7', '00000000-0000-0000-0000-000000000000', '3ab564a7-1d2c-4c43-91e3-a191016cbe67', 'cds') insert menuitems values ('2d6ed5b3-4c6a-4907-8571-a196009727d7', '00000000-0000-0000-0000-000000000000', '3ab564a7-1d2c-4c43-91e3-a191016cbe67', 'dvds') insert menuitems values ('3a1096e7-5fd2-4f77-9729-a19600973bdf', '2d6ed5b3-4c6a-4907-8571-a196009727d7', '3ab564a7-1d2c-4c43-91e3-a191016cbe67', 'movies') insert menuitems values ('5c2ac1f8-dee0-448b-b538-a19600f0839b', '00000000-0000-0000-0000-000000000000', '3ab564a7-1d2c-4c43-91e3-a191016cbe67', 'cassetes') go  declare @menuid uniqueidentifier set @menuid = '3ab564a7-1d2c-4c43-91e3-a191016cbe67' ;with  cte (menuitemid, pid, lvl)       (     select menuid menuitemid, cast(null uniqueidentifier) pid, 1         menus menuid = @menuid     union     select menuitems.menuitemid             , pid = case menuitems.pid when '00000000-0000-0000-0000-000000000000' @menuid else menuitems.pid end             , cte.lvl + 1          menuitems             inner join cte on cte.menuitemid = menuitems.pid         menuitems.menuid = @menuid     ) select * cte

edit: result should this:

3ab564a7-1d2c-4c43-91e3-a191016cbe67    null    1 d3b92d1b-7afa-4e11-af01-9f6a00b1b2a9    3ab564a7-1d2c-4c43-91e3-a191016cbe67    2 70575c44-1ef8-497f-b7b8-a18300f645d7    3ab564a7-1d2c-4c43-91e3-a191016cbe67    2 2d6ed5b3-4c6a-4907-8571-a196009727d7    3ab564a7-1d2c-4c43-91e3-a191016cbe67    2 5c2ac1f8-dee0-448b-b538-a19600f0839b    3ab564a7-1d2c-4c43-91e3-a191016cbe67    2 3a1096e7-5fd2-4f77-9729-a19600973bdf    2d6ed5b3-4c6a-4907-8571-a196009727d7    3

thanks in advance!

kardo

the problem seems in 

 inner join cte on cte.menuitemid = menuitems.pid

because menuitemid , pid not match.

provide table structure , sample data further analysis , simulate problem actually.


Comments

Post a Comment

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

i force app's home screen portrait , other views landscape. apple rejects app if this? if not , how force first screen launch in portrait mode? , force other screens launch in landscape. this not duplicate question search every possible answer in website. i force app's home screen portrait , other views landscape. apple rejects app if this? no apple not reject app this. have lot of apps doesn't support orientation. forcing on 1 screen won't cause app reject. if not , how force first screen launch in portrait mode? , force other screens launch in landscape. can on write : -(nsuinteger)supportedinterfaceorientations; function.
Read more

java Extracting Zip file -

i'm looking way extract zip file. far have tried java.util.zip , org.apache.commons.compress, both gave corrupted output. basically, input zip file contain 1 single .doc file. java.util.zip: output corrupted. org.apache.commons.compress: output blank file, 2 mb size. so far commercial software winrar work perfectly. there java library make use of this? this method using java.util library: public void extractzipnative(file filezip) { zipinputstream zis; stringbuilder sb; try { zis = new zipinputstream(new fileinputstream(filezip)); zipentry ze = zis.getnextentry(); byte[] buffer = new byte[(int) ze.getsize()]; fileoutputstream fos = new fileoutputstream(this.tempfolderpath+ze.getname()); int len; while ((len=zis.read(buffer))>0) { fos.write(buffer); } fos.flush(); fos.close(); } catch (filenotfoundexception e) { // todo auto-generated catch block ...
Read more

C# WinForm - loading screen -

i ask how make loading screen (just picture or something) appears while program being loaded, , disappears when program has finished loading. in fancier versions, have seen process bar (%) displayed. how can have that, , how calculate % show on it? i know there form_load() event, not see form_loaded() event, or % property / attribute anywhere. all need create 1 form splash screen , show before main start showing landing page , close splash once landing page loaded. class splashform { //delegate cross thread call close private delegate void closedelegate(); //the type of form displayed splash screen. private static splashform splashform; static public void showsplashscreen() { // make sure launched once. if (splashform != null) return; thread thread = new thread(new threadstart(splashform.showform)); thread.isbackground = true; thread.setapartmentstate(apartmentstate.sta); thread....
Read more