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.
I have a after update trigger that calculates one of the columns based on other column values. It has to be a trigger and only fires an update statement against the table itself when the column has changed. At a simple level it is doing something similar to the code below
IF UPDATE(Col1) update MyTable SET Col2 = Col1 WHERE Col2 <> Col1
It works everywhere except on one site where the trigger causes itself to recurse until it reaches the 32 level error. It can be fixed by checking whether there and any records in the inserted table at the top. Like so.
IF EXISTS (SELECT * FROM inserted) begin IF UPDATE(Col1) update MyTable SET Col2 = Col1 WHERE Col2 <> Col1 end
However, I would like to know whether there is some system setting other than "nested triggers"Â that I am missing that would cause the behaviour.
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
Another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2347749&SiteID=1) got me wondering: what exactly is this "infinite clickthrough" feature that SSRS Enterprise Edition has? Searching for "infinite clickthrough" yields little but frustration - nearly all of the hits are merely pages explaining that Enterprise Edition has it and the other editions don't, without ever describing what exactly it is.
Near as I can tell, "infinite clickthrough" refers to a feature of ReportBuilder when used in conjunction with Report Models on SSRS Enterprise whereby ReportBuilder will synthesize a clickthrough report on the fly, and has nothing to do with the ability to build "clickthrough" reports using links (the "Navigation" tab on the Properties of most any reporting services object).
This code is from BOL (in index type: "DDLs-SQL Server"): Take a look at error handling .. what happens if one of the three cmd.execute within the [Done:] hanlde fails?
It looks to me like we would have an infinite loop! .. am I missing something here?
BOL CODE EXAMPLE: ----------------------------- Dim Cn As New ADODB.Connection Dim Cmd As New ADODB.Command
' If the ADOTestTable does not exist, go to AdoError. On Error GoTo AdoError
' Set up command object. Set Cmd.ActiveConnection = Cn Cmd.CommandText = "DROP TABLE ADOTestTable" Cmd.CommandType = adCmdText Cmd.Execute
Done: Cmd.CommandText = "SET NOCOUNT ON" Cmd.Execute Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))" Cmd.Execute Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')" Cmd.Execute Cn.Close Exit Sub
AdoError: Dim errLoop As Error Dim strError As String
' Enumerate Errors collection and display properties of ' each Error object. Set Errs1 = Cn.Errors For Each errLoop In Errs1 Debug.Print errLoop.SQLState Debug.Print errLoop.NativeError Debug.Print errLoop.Description Next
GoTo Done
End Sub -------------------------------------------------------
I have a query that runs every night. It has been fine for months. Yesterday it started to run infinately.
When i execute it it never returns.
BUT when i add a top clause to othe query it works, now in my experience this normally happens because you are now eliminating the "bad" row with the top clause, so the join that causes it to run infinately now never occurs.
HOWEVER i run the exact query with a TOP 730 cluase and it only return 720 rows, so i am not eliminating any rows ?
Basically i have a query that runs infinately but if i add a top clause that does eliminate any rows it works ?
But if i increase the top cluase to 800 it runs inifnatley again, but it only returns 720 rows ?
I am stumped, can anyone shed some light on this please.
I have an infinite loop in a trigger I and I cant reslove it.
In my system the user updates a stock table from the GUI and on the update I need to check values to see if I need to add records to a StockHistory table. For Example: If the user changes the grade of Product X from A to B then I need to add a new line in StockHistory for product X grade A that decrements the total number of products in the warehouse. Similary I need to increase the quantity of stock for Product X grade B.
I had the trigger working for single updates but now when stock is added to the database (from another db) it has status of 'New'. This isn't actually 'in stock' until the user sets the status to 'Goods In'. This process will then update the status for all records in the category. This caused my trigger to fail as the 'inserted' table now contains many records.
Now the problem I have is the trigger is in an infinite loop. It always shows the id of the first record it finds and the @Quantity values increases as expected. I've taken all my procesing code out of the trigger and adding some debugging stuff but it still doesnt work:
CREATE TRIGGER [StockReturns_on_change] ON [dbo].[StockReturns] FOR UPDATE AS
DECLARE INDIVIDUAL Cursor --- Cursor for all the rows being updated
FOR SELECT Id FROM inserted
OPEN INDIVIDUAL
FETCH NEXT FROM INDIVIDUAL INTO @Id
select @Quantity = 1
print @@FETCH_STATUS print @Id print @Quantity
WHILE @@FETCH_STATUS = 0 begin
select @Quantity = @Quantity + 1
print @@FETCH_STATUS print @Id print @Quantity
-- Get the next row from the inserted table FETCH NEXT FROM INDIVIDUAL INTO @Id
End -- While loop on the cursor
-- no close off the cursors CLOSE INDIVIDUAL DEALLOCATE INDIVIDUAL
Hi i have a cursor in a Stored Procedure. The problem is that it's poiting to the first row and causing an infinite loop on it. How can i stop this and make it go to all rows. Here is my code.
Declare @CountTSCourtesy int Declare @WaiterName nvarchar(100), @CursorRestaurantName nvarchar (100) Declare waiter_cursor CURSOR FOR
SELECT new_waiteridname, new_restaurantname FROM dbo.FilteredNew_CommentCard Where new_dateofvisit between @FromDate and @ToDate and new_restaurantname = @Restaurant Open waiter_cursor FETCH NEXT FROM waiter_cursor into @WaiterName,@CursorRestaurantName While @@FETCH_STATUS=0
BEGIN Exec WaitersCountExCourtesy @WaiterName,@CursorRestaurantName
END Close waiter_cursor Deallocate waiter_cursor END
I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL DROP TABLE #tTable CREATE TABLE #tTable
I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.
The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.
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
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!
now the scenario is : the user A is from the company "Alpha" he introduces user B, who registers in the system his company bcomes "self", now B inturn refers user C who also registers in the system and his company is now again "self". Now I need to generate a report of number of users that have registered under one company, for eg. for the company "Alpha" no of users becomes 2 since A refered to two users and both of them have registered.
I m stuck with the query. thanks in advance... regards, Harshal
id name ----- ------------------------------------------ 1 My top parent node 2 My second node 3 my child node
If I do a search for say 'my child node' I need to display where 'my child node' is in relation to the hierarchy. i.e i need to show it's parent and if that has a parent I need to show its parent etc... and continue until there are no more parents left
So using the table details if i search for 'my child node'
I need to display this : My top parent node -> My second node - > my child node
The id for 'My top parent node' doesn't exist in tblparent because it is the top parent
I am wondering if there is some type of recursive query to return the values I want from the following database.
Here is the setup:
The client builds reptile cages.
Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.
The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.
PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.
Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb
use pubs sp_configure 'nested triggers',1 go reconfigure go alter database pubs set RECURSIVE_TRIGGERS ON go create table abcd (recid int) go create trigger abcd_trigger on abcd instead of insert as begin declare @recid int select @recid = recid + 1 from inserted insert into abcd values (@recid) end go insert into abcd values (1) go select * from abcd go drop trigger abcd_trigger drop table abcd go
Why does this insert value as 2 even though I have enabled recursive triggers.... Gurus .. any answers????
And as for changing the database options ... please check what they are before executing this post so that you can reset them later.
