CREATE TABLE [dbo].[TransactionComponents](
[pkTransactionComponent] [int] IDENTITY(1,1) NOT NULL,
[pkTransactionID] [int] NOT NULL,
[ComponentID] [int] NULL
) ON [PRIMARY]
With the following data:-
INSERT [dbo].[TransactionComponents]([pkTransactionID], [ComponentID])
SELECT 1,5
UNION SELECT 1,6
UNION SELECT 1,7
UNION SELECT 1,8
[Code] ....
pkTransactionID and ComponentID both link to the same column on another table this enables a many to many relationship, what I need to figure out is a complete tree of relationships from one of the ID's in it. I think I need to write a recursive CTE to achieve this but I am not entirely sure how to write it. Below is my attempt:-
DECLARE @ID INT
SET @ID = 1;
WITH
cteTxHeirachy (TxID, RelTxID, TxLevel)
[Code] ...
This returns:-
15
16
17
18
19
110
But the following are missing:-
10 2
2 11
2 12
3 and 4 should not be returned. I figured if I added the code that is commented out in the CTE that should give me everything but I think I get caught in an infinite loop.
in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as ( select 0 [iterations], 1e+0 [computed] union all select iterations+1,dbo.f(computed) from where rCTE where iterations < 30 ) select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
CREATE TABLE #tree ( Childid varchar(20), Parentid varchar(20) ) INSERT INTO #tree (Childid,ParentId) SELECT '123' , null UNION ALL SELECT '456' , '123' UNION ALL SELECT '789' , '456' UNION ALL SELECT '870' , '456' UNION ALL SELECT '985' , '870';
I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).
product details are stored in `[products]` articlegroups are defined in `[articlegroups]` and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`
Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:
Now I can get the separate fields via a statement like this:
SELECT ga.slug_nl as slug_nl_0 FROM articlegroups ga INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id INNER JOIN products gp on gp.id=pcm.artikelid WHERE gp.id=2481446
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
The MLID in the header table will be generated automatically.All the Parents will be stored in the HEADER and their childs in the DETAIL.When a child is added to a parent,the Parent's MLID will be stored in the MLID field in the DETAIL table with the newly added child.That child will come to the PARENT table when a child is added to that.The MLITEM id in the parent table can be repeated when that item undergoes a rivision.But the MLID for this will be a new one.An item in the Parent Table can have any number of childs and these childs can have any number of children(there is no limit for the level.)
Now if i select an item id '1000' (for example from the Header Table) with a concatstring (it could be without a concat string also).all its childs and their children should be printed in a report like the following
************************************************** *************************** I NEED TO CREATE THE TREE USING BOTH THE HEADER(MSTHDRML) AND THE DETAIL TABLE(MSTDTLML) ************************************************** ***************************
How can this be done.Is it necessary to use a recursive function in a stored procedure to generate this ...... i have never used Recursive function in SQL SERVER Stored Procedures.Can anyone help me on this(with Code).if not stored procedure, then what else can be done for this.
Dear fellows, Can anybody tell me how can i apply recusive/Tree query using select statement. For example I've a table structure for an Organization as follows:
emp_id emp_name supervisor_id ---------- --------------- ------------------- 101 ZAFIAN 102 BRUNNER 101 108 CALLAHAN 102 105 RUSSO 102 110 SIM 102 103 DUELL 101 and so on
1. How can I get the above records in Hirarchical format starting from top or from anywhere else in the hierarchy?
In Oracle it can be done as follows: SELECT emp_id,emp_name,supervisor_id FROM employee_tbl CONNECT BY supervisor_id = PRIOR emp_id START WITH supervisor_id is null;
Please reply me at the following address if possible: faisal@visualsoft-inc.com
INSERT INTO Details SELECT '','7419-01',0 union all SELECT '7419-01','44342-00',1 union all SELECT '7419-01','45342-00',1 union all SELECT '7419-01','46342-00',1 union all SELECT '7419-01','47342-00',1 union all SELECT '7419-01','48342-00',1 union all SELECT '7419-01','49342-00',1 union all
[code]....
From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.
I have searched the net for atmost two days to find the solution of this problem but we not able to get the solution. I would appritiate if any one could help me in solving this issue:
I want to write a trigger for deleting the EMPLOYEE with EMPID=1 and the trigger should delete all the employees as there is cascading among them i.e EMPID 1 is the Manager of EMPID=2 and so on..
I found a solution at: http://msdn2.microsoft.com/en-us/library/aa902684(sql.80).aspx but the solution does not work when i try to implement it . It deletes the record for abc,xyz in the above table but rest are not deleted by the trigger.
Suppose there's a table named [Category], which has 2 columns: CategoryID int, ParentCategoryID int
Each category, except the top most, has a parent category, it's a 1-to-n parent-children relationship. Now I want to write a stored proc./function that accepts a CategoryID input parameter, and output all the descendent CategoryIDs (son, grand son, ...). How to do that in MSSQL 2000?
I have created a package which has to move huge volume of data in chuncks from source to target with a few sorter transformations and many lookup transformation. Once a chunck is completed the Execute SQL Task in the package updates the etldates into a control table against the chunkid. I want this run in a loop, as on completion of a chunk another should start and follow the same process until there are no more chuncks in the control table. First i execute the package manually, then i have written a trigger to call the SQL Server Job (etl) on update of the dates for the chunkids in the control table. The job when being called by the on update trigger is throwing up the following error. "The job failed. The Job was invoked by User fwt obin. The last step to run was step 1 (Package1)". Am i missing something basic here?
PS: So i tried putting the package into a for each loop container, performance suffers. A chunck which used to take 2 hours is taking more than 12 hours to complete. Then i removed sorter transformation and used db sorting in my source OLE db. Then thought of cacheing the lookups but did not know how and what to give so i gave up. While iam here, DB sorting/SSIS sorting? which is better?
I would like to create a store procedures that will return me a full path by passing a PathID
I started with this code:
DECLARE @path_id int SET @path_id = 5; WITH fullPath (PathID, ParentPathID, Path) AS ( SELECT PathID, ParentPathID, Path FROM tblPath WHERE PathID = @path_id UNION ALL SELECT tblPath.PathID, tblPath.ParentPathID, tblPath.Path AS Path FROM tblPath JOIN fullPath ON tblPath.PathID = fullPath.ParentPathID ) SELECT * FROM fullPath
This code will return this: 1 NULL D: 2 1 Sections 3 2 Bin 4 3 Data 5 4 FinancialReport.doc
What I would like to get is something like this: D:/Sections/Bin/Data/FinancialReport.doc
Any help would be really appreciated. Lost too much time on it already. Thanks, pharvey
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
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.
I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:
declare @tv table (member varchar(255), relatedTo varchar(255)) insert into @tv select 'John', 'Mary'union all select 'Mary', 'Jessica' union all select 'Peter', 'Albert' union all
[Code] ....
I would like my result to look like this:
familyID Name 1 John 1 Mary 1 Jessica 1 Fred 2 Peter 2 Albert 2 Nancy 3 Abby 4 Joe 4 Frank
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...
