.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

CTE - Recursive SQL

Posted By:      Posted Date: September 04, 2010    Points: 0   Category :Sql Server
 
Hey Guys, Hope you can help me out over here. We are building a datamart for a large group of users. They will use BOE webi's to generate reports with. One of the tables is a category structure (see picture below).The strongest requirement is that they will be able to aggregate information on any level -including -all- sublevels. For that to work, we need to write a recursive query that outputs all categories and all of their children (and grandchildren). So per "branch" if you will. I knew how to write that recursion in .Net but not how to do it in SQL. Before SQLUSA & Ramireddy's repsonses I had no clue how to implement that with CTE. -So thanks you guys! I've added my solution below. The problem i am running into now is the maxrecursion. When using the CTE query below on the actual database it runs into the limitation of 32767 iterations. Anyone who can help me solve this, using the example below?   Sample Table SQL If exists (select top 1 * from sysobjects where xtype='U' and name = 'Category') Drop Table Category Go Create Table Category( CategoryId Numeric(18,0) Null, ParentCategoryId Numeric(18,0) Null, ) Go Insert into Category(CategoryId,ParentCategoryId) Values (1,0) Insert into Category(CategoryId,ParentCategoryId) Values (2,1) Insert into Category(CategoryId,ParentCategoryId) Values (3,2) Insert into Ca


View Complete Post


More Related Resource Links

TreeView (icon+check+label+textbox) binded to a recursive dataset

  
Hello dudes, I have a DataSet with a DataTable and I want to populate a TreeView with it's contents:Id, IntParentId, IntName,StringIcon, ???Checked, IntValue, Intthe nodes must look like:   (icon) [checked] - Name |Value|there are 2 fields updatable, checked and value, and changes made by user in the tree must update the datatable/rowI was the last 2 day looking for how to build a tree like this and have no ideas..... tks :)

ssrs recursive parameters

  
What are recursive parameters in ssrs?

Importing data from recursive XML elements

  
The requirement is to import data (to SQL Server 2005) from a recursive xml schema. The problem is, the element names are positioned recursively. For example, the element would have another element and so on. The depth of occurences are not known.  This data has to be imported and stored in a flattened form Here is the sample xml: <ROOT id="1"> <TIME>2010-02-24 22:28:20</TIME> <REGION code="SW">South West</REGION> <DOMAIN code="LEGL" seq="1">LEGAL</DOMAIN> <YEAR>2009</YEAR> <NOTICE id="1"> <SECTION lo="3" hi="3" seq="680" code="3">SECTION 3</SECTION> <CASE id="79524D7A-D55D-11DD-BF1B-36609DFF4B22" level="1" placeholder="N" linkable="N">Level 1 Section 3 case 1.<CASE id="79538DA2-D55D-11DD-BF1B-36609DFF4B22" parent_id="79524D7A-D55D-11DD-BF1B-36609DFF4B22" level="2" placeholder="N" linkable="N">Level 2 Section 3 case 1.<CASE id="7957BA94-D55D-11DD-BF1B-36609DFF4B22" parent_id="79538DA2-D55D-11DD-BF1B-36609DFF4B22" level="3" placeholder="N" linkable="N">Level 3 Section 3 case 1.<CASE id="7954B7EA-D55D-11DD-BF1B-36609DFF4B2

Class with recursive call to that calss.

  
Dear all,I have a class with a property who is a recursive call to the same type of Class. Is this a correct way of working ?The code :public class DataObject { public string Name { get; set; } public string DataType { get; set; } public int IntValue { get; set; } public string StrValue { get; set; } public DataObject DataValue { get; set; } public DataObject() { Name = string.Empty; DataType = string.Empty; IntValue = 0; StrValue = string.Empty; DataValue = new DataObject(); } } I got an abort exception while creating an object from this class.

Strange behaviour of a query using a recursive CTE

  
Hi, I have a problem with a query. The situation is a bit complicated but I'll try to explain it the best I can. Short story: I have a .NET application that executes a recursive query (using CTE) and then uses a DataReader to read each row's each field one-by-one using the GetValue() method. With specific parameters this query behaves quite fast but with other parameters it can take more than a second to read a row's field (instead of the usual ~0.00001 second). Long story: The database: I have 4 tables with the following columns: 1. Entity table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255)), Description (nvarchar(2,000)). 2. Relation table: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), ParentEntityId (int), ChildEntityId (int), Ordinal (int). 3. Property table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255), Description (nvarchar(2,000)). 4. EntityProperty table with 10,000 rows: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), EntityId (int), PropertyId (int). The Entity table has 100,000 rows, the Relation table has 100,000 rows (each entity has a relation), Property table has 22 rows and the EntityProperty table has 2,010,000 rows (properties with Id from 1 to 20 are assigned to all entities and properties with

Strange behaviour of a query using a recursive CTE

  
Hi, I have a problem with a query. The situation is a bit complicated but I'll try to explain it the best I can. Short story: I have a .NET application that executes a recursive query (using CTE) and then uses a DataReader to read each row's each field one-by-one using the GetValue() method. With specific parameters this query behaves quite fast but with other parameters it can take more than a second to read a row's field (instead of the usual ~0.00001 second). Long story: The database: I have 4 tables with the following columns: 1. Entity table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255)), Description (nvarchar(2,000)). 2. Relation table: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), ParentEntityId (int), ChildEntityId (int), Ordinal (int). 3. Property table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255), Description (nvarchar(2,000)). 4. EntityProperty table with 10,000 rows: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), EntityId (int), PropertyId (int). The Entity table has 100,000 rows, the Relation table has 100,000 rows (each entity has a relation), Property table has 22 rows and the EntityProperty table has 2,010,000 rows (properties with Id from 1 to 20 are assigned to all entities and properties with

Recursive Function

  
I need a way to sum a field until I get a specific value. I have a subset of records ~8-20 sales records with 2 different discount rates applied on the same invoice. I know the percentage of the discounts is say 10% for a toal of $25.20 and 40% for a total of 36. Given this I know that the item(s) receiving 10% discount totaled $252 and the item(s) that recieved 40% discount totaled $90. Does anyone have a way to take this information and find the transacation(s) that match the total dollars. It does not have to be 100% accurate but I can only apply one discount to each transaction record. Any insight you may have on this will be greatly apprecaited. Thanks.

Recursive fallback not allowed for character \u003F.

  
I added a new link to a master page on my website. Immediately after saving, I started getting this error:Recursive fallback not allowed for character \u003F.Parameter name: charsI remove the new link and I still continue to get the error. You can see this for yourself here:http://www.funinfused.com/hypership/Any ideas? I couldn't find anything good on this on the web.

Recursive fallback not allowed for character \u003F.

  

Hi, 

My asp.net website suddenly showed the error message listed below. I Googled but didn't find any effective information.  The hosting company claimed that they didn't change any set up on the server. Now I'm runnning out of ideas :(.

Could anybody kindly provide some suggestions on how to deal with it? I would like to spend some time on it however right now I don't know where to focus.

Thanks in advance! Any thoughts/ideas/examples etc would be greatly appreciated. 

Gabriel

 

 

Recursive fallback not allowed for character \u003F.
Parameter name: chars

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Recursive fallback not allowed for character \u003F.
Parameter name: chars

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified

Recursive XQuery Order by

  

I got an recursive XML in my database generated by my stored functions. Simple example:

<MEMBER POSITION="9">
 <MEMBER POSITION="4">
  <MEMBER POSITION="4"/>
  <MEMBER POSITION="1"/>
 

Is PropertyChangedEventManager able to get used recursive?

  

Hi,

I'm using WeakEvents to deliver PropertyChanged events between various models. That's works fine. But when one of the reactions is a change of another property what occurs another PropertyChanged delivery I get a fatal ExecutionEngineException 0x80131623. The delivery of the first event works fine and the second one fails.

What can I do? Is it an failure of my construction, a bug within the .NET framework 4.0 or programming voodoo?

Best regards,

Torsten


select Parent/Child id recursive query

  

Table

Rank chid parid

7       6

5       4

4      3

3      2

2      Null

 

My output should be

 

Rank chiid parid

1        5       4

2        4      3

3        3      2

4        2      Null

 

parid : 6 not matching with child id, it should not come in select query

parid : 2 has child id , so it should be appear in the select query

Rank as row_number()

basically , last parent id should be next row of child id, O

only matching parid should be next row as child table..continues.....

I need some type of sub query , I dont want link please.

Thanks

 

Filter SharePoint list based on ID and scope as Recursive in DataFormWebPart

  

The following is the code in the DataFormWebPart

<DataSources><SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" UseServerDataFormat="true" selectcommand="&lt;View&gt;&lt;ViewFields&gt;&lt;FieldRef Name=&quot;ContentTypeId&quot;/&gt;&lt;FieldRef Name=&quot;Title&quot;/&gt;&lt;FieldRef Name=&quot;File_x0020_Type&quot;/&gt;&lt;FieldRef Name=&quot;RoutingEnabled&quot;/&gt;&lt;FieldRef Name=&quot;Category&quot;/&gt;&lt;FieldRef Name=&quot;Approach&quot;/&gt;&lt;FieldRef Name=&quot;Channel&quot;/&gt;&lt;FieldRef Name=&quot;Competency_x0020_Pillar&quot;/&gt;&lt;FieldRef Name=&quot;Contact&quot;/&gt;&lt;FieldRef Name=&quot;Cost&quot;/&gt;&lt;FieldRef Name=&quot;Cost_x0020_Description&quot;/&gt;&lt;FieldRef Name=&quot;Country&quot;/&gt;&lt;FieldRef Name=&quot;Editor_x0027_s_x0020_choice&quot;/&gt;&lt;FieldRef Name=&quot;Execution_x0020_Plan&quot;/&gt;&lt;FieldRef Name=&quot;External_x0020_Links&quot;/&gt;&lt;FieldRef Name=&quot;Issue&quot;/&gt;&lt;FieldRef Name=&quot;Lessons&quot;/&gt;&lt;FieldRef N

How to use ajax for recursive call of a function in asp classic

  

I have a asp page with parent and child relation  task when i click on + sign the task with the parent id of the first should open. it may go up to nth level

e.g 

1 >2

1>3

2>4

2>5

2>6

5>7

7>8

so to go to 8 we have 1>2>5>7>8

so first i have to click on 1 which will open its child then i have to select 2 which opens its child.

If i am doing it on same page recursively it hits performance.

as it is a asp page and each time it is hiting the server so i have to use ajax and build the page out side and append this to my parent page 

can any one help me in this regards,

 


Please Mark the answers and give points it will help users Thanks & Regards, Kamal Agarwala

How to call a ajax recursive function

  

I have a asp page with parent and child relation  task when i click on + sign the task with the parent id of the first should open. it may go up to nth level

e.g 

1 >2

1>3

Recursive calling in SQL

  

The version is SQL Server 2008

I have table

empid       mgrid       firstname  lastname
----------- ----------- ---------- --------------------

Write a solution using a recursive CTE that returns the management chain leading to Zoya Dolgopyatova (employee ID 9)

The result should be:

empid       mgrid       firstname  lastname
----------- ----------- ---------- --------------------
9           5           Zoya       Dolgopyatova
5           2           Sven       Buck
2           1           Don        Funk
1           NULL        Sara       Davis


Recursive inventory computations in MDX

  

Hi,

 I need help with efficient MDX for the following computation. Four measures of interest, over time

Replenishment, TargetInventory, Demand, BeginningOnHand, EndingOnHand.

Demand and TargetInventory are measures which are defined elsewhere, and available at the point where the below computation needs to take place.

At time t=0, BeginningOnHand(t=0) = 50, Demand(t=0) = 150, TargetInventory(t=0) = 100

Replenishment(t=0) = Demand(t=0) + TargetInventory(t=0) - BeginningOnHand(t=0) = 150 + 100 - 50 = 200

EndingOnHand(t=0) = BeginningOnHand(t=0) + Replenishment(t=0) - Demand(t=0) = 50+200-150 = 100.

 

For future time buckets t > 1, the computation is defined as below:

Replenishment(t) = Demand(t) + TargetInventory(t) - BeginningOnHand(t)

BeginningOnHand(t) = EndingOnHand(t-1)

EndingOnHand(t) = BeginningOnHand(t) + Replenishment(t) - Demand(t)

I need to solve the above computation in time, with the recursion in time stopping at t=0, effectively in MDX. Any help will be greatly appreciated.

Rajeev


RK
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend