I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on.
1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record?
2. I need to be able to do a search in this table. Example:
Table: tbComponent Columns: Co_ID Integer Co_Co_ID Integer Co_Name Text Co_Attribute Text Co_Category Text
Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories.
A typical group of records could be something like this:
Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category
1 1 Car Blue Ford 2 1 Body Steel Parts 3 2 Door Front Parts 4 3 Invoice April 1 1999 Accounting
Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be:
If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them.
Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation.
I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?
I have a recursive query, using common table expressions, like this: WITH TaskHierarchy (GUID, ParentGUID, Title, Complete, HierarchyLevel) AS ( SELECT GUID, ParentGUID, Title, Complete, 1 HierarchyLevel FROM Task WHERE ParentGUID = @GUID
UNION ALL
SELECT t.GUID, t.ParentGUID, t.Title, t.Complete, th.HierarchyLevel + 1 HierarchyLevel FROM Task t INNER JOIN TaskHierarchy th ON t.ParentGUID = th.GUID ) SELECT (COUNT(*) - SUM(CAST(Complete AS INT))) Outstanding FROM TaskHierarchy The result is a number. I need access to this number. Ideally, I would like to store it in a variable, but anything would work as long as I can access it after the query.Anyone know of a way?
I have a database which stores information about organisms collectedduring sediment toxicology research. For each sample, organisms insediment are collected and identified taxonomically (Order, Family,Genus, Species).Taxonomy lookup information in the database is stored in a recursivetable in the form:TSN (taxa serial number)Rank (Order, Family, Genus, Species)NameParent_TSN (related Taxa at higher taxonomic level)When the number of a particlar organism collected is entered into thedatabase, the count is stored along with the lowest level TSN theorganisms were identified to.Okay - now the problem. Depending on the type of analysis being done,a user may want organism counts at the lowest level, or rolled up to ahigher taxonomic level (usually Family). Can I write a recursivefunction which will cycle through the Taxonomy database, and providethe name of the organism at the appropriate taxonomic level? Is this areasonable approach with regard to speed and efficiency?Something Like:SELECT sample_id, 'Get Name Function(Rank, TSN)', Sum([count]) ASNoTaxa FROM dbo.tblbenthicResults could then be grouped and summed on the Name, to summarisedata for each sample/taxa.Is this a reasonable approach? Or is there a better one? Did I explainthe problem well enough?Thanks in advance,Tim
I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2) SELECT @Start=01.00, @End=20.00 ;WITH NumberSequence( Number ) AS ( SELECT @start as Number UNION ALL SELECT Number + 00.01 FROM NumberSequence WHERE Number < @end
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved.
John Smith
- Jacob Jones - Lisa Thompson - Samuel Barber
- Paul Smith - John Jackson
Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber.
Here are the tables:
Users holds the user_id, first_name, last_name, and reports_to_user_id. User_Roles holds the user_id, role_type_id Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest.
I have a recursive CTE on an inline table valued function. I need to set the MAXRECURSION option on the CTE, but SQL Server is complaining with "Incorrect syntax near the keyword 'OPTION'".
It works fine on non-inline function. I couldn't find any documentation indicating this wasn't possible.
I can use the MAXRECURSION option in call to the function
SELECT * FROM MyFunction () OPTION ( MAXRECURSION 0 )
but that means that the user needs to know the "MyFunction" uses recursive CTE, which defeats the purpose of the abstraction.
Hi All, I have a requirement here to import data from XML file to SQL Database. The XML schema contains of various elements and one of the element is recursive ie. Parameter node contains parameter node within it and it can have n number of iterations. I have given the sample schema below:
But all the nodes contain the data which has to be imported to a single table dbo.Parameters. I cannot use Union ALL since i dont know how many iterations I will have in the file. Is there any way to do this operation in Data Flow Task using XML Source? Can anyone help me on this?
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
we have a table called evaluation_questions, the table has following fields
queId,Question, level, parentId
the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading, the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question. where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....
so English is a heading as level is 0 and has no parent as parentId is also 0. Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English same is the case with writing where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.
Output:
Now What i want is to retrieve the all the questions and headings under a specified heading.like if i pass parentID as a parameter to stored procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
I need to help in writing stored procedure to recursively delete Categories and ads for those categories Simplified table views: Category: CategoryID, Name, ParentCategoryID Ads: AdID, Name, CategoryID Please help
Hello Everyone, I have a purchase order table that holds say 2 columns. PO and OrgPO. That is Purchase Order # and Original Purchase Order # respectively. Assume i have the following rows in the table. PO OrgPO -- ------ po1 NULL co1 po1 co2 co1 co3 co2 po2 NULL cpo1 po2 po3 NULL Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg, PO LastCO -- ------ po1 co3 po2 cpo1 po3 po3 Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.
I have problem is getting list of all the Tree Level Employees.here is the my table structure
Manager ~ SubOrdinate 1 ~ 2 1 ~ 3 1 ~ 4 2 ~ 5 2 ~ 6 2 ~ 7 3 ~ 8 3 ~ 9 4 ~ 10 5 ~ 11 SO ON I NEED THE WAY , HOW I CAN GET THE HIRE LEVELS ( EG IF I PASS MANAGER(EMPLOYEE NO) 1 IT SHOULD DISPLAY HIS 1 LEVELS AND 2, 3 SO ON LEVELS OF LEVELS OUTPUT COULD BE ,
PASSED EMPLOYEE NO : 1 LEVEL ~ EMPLOYEE 1 ~ 2 1 ~ 3 1 ~ 4 2.1 ~ 5 2.1 ~ 6 2.1 ~ 6 3.1 ~ 7 SO ON THAT EMPLOYEE RELATED INFORMATION ( ITS LIKE MLM LEVELS) CAN ANYBODY HELP IN THE ,
is there any way to define a recursive cte within another cte. I have defined a recursive cte which works great for finding the different versions of a given item. What I would like to do is to define a set of items for which I want to find the different versions.
Article(articleID,CategoryID,ArticleTitle) Categories(categoryID,ParentID,CategoryTitle) I am trying to retrieve the main category ID for a specific article ID. For example lets say I have this data: Article:
In this example I want to know who is the main category of article 3. The query should return the answer: 1 Thats because:
The article ID 3 is inside category 11. Parent for category 11 is 10. Parent for category 10 is 2. Parent for category 2 is 1 and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category. Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables) Do you have any suggestions for such query? Thanks all.
Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project .. .
I’m desperating due to the complex (for me and also I think for some others) sql query that I need to write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification table that holds this mentioned “custom defined fieds� according to the Classifications table, on where the Products can be found trought the productsClassifications table.
CustomFields can be defined and set for the products, trought his Classifications (instead of define a custom field for each product (that consume a lot of data), I decide to use it as I explain)
I will to know the properly SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:
As example on a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the ProductsFields and Values that has the DBA for the:
· requested ID_Classification
o PAL (ID_Classification: 16)
· AND all the Classifications that belongs above (trought ID_ParentClassification) that are :
o Torrents (ID_Classification: 1) that will show the products values for the “Size�
o Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.
o Dreamcast (ID_Class..:14 ) that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)
Hmnn i show a graphic that i design for (feel to click over to see at correct resolution)
I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com
i have a table like this parentid | childid | description 1 2 blah 1 3 1 4 2 23 2 24 5 8 3 10 and i want to give the parentid 1 and get all the children i have a cursor now like this but i dont know how to make it recursive any help?
1 2 DECLARE @childid nvarchar(50) 3 DECLARE ItemStruc CURSOR FOR 4 5 SELECT cmponent_prt_no , parent_part_no 6 FROM oauser_prod_structure 7 WHERE parent_part_no = @parentid 8 9 OPEN ItemStruc 10 FETCH NEXT FROM ItemStruc 11 INTO @childNum , @parentid 12 13 WHILE @@FETCH_STATUS = 0 14 BEGIN 15 16 print @childNum +'is the a child to: ' +@parentid
Does anyone know how to do an sql recursion queries?
I believe it involves a view with a union.
I have a User Table and in that table i have a employee_id and a boss_id. What i'd like to do is to find all employees under a certain boss. For example,
Employee_ID Boss_ID 1 2 1 3 4 3 5 2
So if i'd like to know who are under the employee_id = 1 it will return employee_id 2 and 5 since employee 2 also is the boss of employee_id = 5.
I have a list of categories that I have to find the path to each my table is set up like ID CategoryNum CategoryName ParentID 1 AA Top Level 1 02 AB Top Level 2 03 BA Second Level 1 1I need my query/stored proc to return Tope Level 1/Second Level 1 if I pass in the integer of 3I can do this in programming but cant seem to wrap my head around it in SQL ServerTIA for the help
I'm using exec sp_dboption 'ilgadmin', 'recursive triggers',true
I made
create trigger dbo.templates_ondelete on templates for delete as begin delete pages where templateid = (select [id] from deleted) end go
create trigger dbo.pages_ondelete on pages for delete as begin delete pageItems where pageid = (select [id] from deleted) end go
The second trigger must be started by the first one.
But it doens't work because if I do delete from templates where id = 2 more than one page (7 pages) are deleted and the second trigger doesn't work (it can only delete one by one in pageitems!)
Is there any option in SQL Server that I forgot or can I use an other methode
Hey there, Hopefully someone has a sec to help me out. I have a pretty tough query here for ya.
I want to turn this:
Parent | Child Fred | Mark John | Cindy Mark | John John | Suzy
into this:
Ancestor | Level | Descendant Fred | 1 | Mark Fred | 2 | John Fred | 3 | Cindy Fred | 3 | Suzy Mark | 1 | John Mark | 2 | Cindy Mark | 2 | Suzy John | 1 | Cindy John | 1 | Suzy
I want to do this in a single select statement. No loops or cursors. Please tell me you are as stumped as I am so that I don't feel so dumb.
Write a query which reports each employee's name and -- experience level and the name of his/her supervisor
need help with this query, the ouput is this ;
Employee ExperienceLevel Supervisor ------------------------- --------------- ------------------------- Barbara Kimball Master Brenda Fowler John Gromek Junior Brenda Fowler
(2 row(s) affected)
Enhance your query to report all employees, whether or -- or not they have a supervisor. some help would be gratifull.
Hi, I have a table with 2 fields in it Circuit_ID and Parent_Circuit_ID and I'd like to pass a stored procedure a circuit_ID and get a resultset back of all Circuit_ID's that are associated with the original.
That means it needs to look at each record and do another search on all circuits that have that parent ID, recursively.
Does anyone have any good ideas on the best way to do this.
I have a table of product types, the table is layed like this:
TypeID TypeName ParentID
each type has a ParentID equal to one of the other types TypeID (except the very top level types) to build a hierarchy with several levels.
I need a way of giving a function a single TypeID, and have it return a table that has the the TypeID of every type underneath it in the hierarchy. it could go down several levels (like 10 or more) so it needs to recursively work...
I'm just completely lost...
I can get this to work just fine in VB... but in VB i can just use a nice little for each loop...
I am trying to write a recursive function which allows me to provide a hierarchical structure from an employee table but I am getting an error
Server: Msg 512, Level 16, State 1, Procedure GetEmployee, Line 10 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is there a way in which I can write a recursive function to do this. Or would I need to use cursors, multiple functions??? Any help or examples would be great. Thanks for your help
CREATE FUNCTION GetEmployee(@EmpID as int) RETURNS @Employee Table ( StaffNo int, ParentStaffNo int ) AS BEGIN DECLARE @ManagerID int SET @ManagerID = (SELECT [id] FROM eii.dbo.test WHERE Parent_ID = @EmpID)
IF @ManagerID IS NOT NULL and @ManagerID > '' and @ManagerID <> @EmpID INSERT INTO @Employee SELECT [id],parent_id FROM eii.dbo.test Where [id] = @ManagerID UNION SELECT * FROM GetEmployee(@ManagerID)
Hey all, I am writing a procedure to perform a search against products in a website. It is a hierarchical setup, with websites, catalogs, category hierarchies, categories and products. So I need to get all the products that match the search, that are in categories that are in category hierarchies (category-to-category relationship), that are in catalogs that are in 1 website.
I can get the category that a matching product is in, but from there I need to recursive go up the ladder of category hierarchies until it reaches a point where the parent hierarchy is null, and then join that record with the catalogs that are in the 1 website. Here's what I thought might work:
Code:
SELECT ctlgs_CatalogCategoriesToProducts.* FROM COMMERCE_PRODUCTS INNER JOIN ctlgs_CatalogCategoriesToProducts ON COMMERCE_PRODUCTS.ITEMNO = ctlgs_CatalogCategoriesToProducts.ItemNo INNER JOIN ctlgs_CatalogCategoryHierarchies ctlgs_CCH ON ctlgs_CatalogCategoriesToProducts.CategoryID = ctlgs_CatalogCategoryHierarchies.CategoryID
*** some recursive stuff*** WHILE (ctlgs_CatalogCategoryHierarchies.ParentHierarchyID IS NOT NULL) BEGIN INNER JOIN ctlgs_CCH ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_CCH.ParentHierarchyID END
******
INNER JOIN ctlgs_Catalogs ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_Catalogs.RootHierarchyID INNER JOIN ctlgs_CatalogsToWebsite ON ctlgs_Catalogs.ID = ctlgs_CatalogsToWebsite.CatalogID INNER JOIN ctlgs_Websites ON ctlgs_CatalogsToWebsite.SiteID = ctlgs_Websites.ID WHERE ctlgs_Websites.ID = @website AND COMMERCE_PRODUCTS.BLOCKED = 0 AND ( COMMERCE_PRODUCTS.ITEMNO LIKE @searchTerm OR COMMERCE_PRODUCTS.DESCRIPTION LIKE @searchTerm OR COMMERCE_PRODUCTS.DESCRIPTION2 LIKE @searchTerm )
I've never used WHILE before, so I'm not sure what exactly you can put in there. Any advice would be greatly appreciated, thanks!
