Recursive Cursor
Sep 23, 2004
Hi,
I have 2 tables
tblparent
parent_term_id term_id
-------------- -----------
1 2
2 3
tblname
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
Can anybody help with doing this
Thanks in advance
View 7 Replies
ADVERTISEMENT
Jul 2, 2014
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
[Code] ....
View 9 Replies
View Related
Sep 25, 2015
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
[code]....
View 5 Replies
View Related
Sep 20, 2007
Hi all,
I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:
(id, parentId, label)
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
now i want to copy object 3 (UK) and it's children, so i would get
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
5, 0, UK_copy
6, 5, London_copy
I have this sproc:
Code Snippet
alter proc CopyObject
(@ObjectId int,
@NewParentId int)
as
declare @NewId int,
@NewName varchar
select @NewId = max(Id) + 1 from Object
select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId
-- copy object
INSERT INTO [Object]
([Id]
,[Name]
,[ParentId]
select @NewId,
@NewName,
@NewParentId
from [Object]
where Id = @ObjectId
-- copy children and set their parent to the newly created object
declare c cursor fast_forward for
select Id
from [Object]
where ParentId = @ObjectId
declare @ChildId int
open c
fetch next from c into @ChildId
while @@fetch_status = 0
begin
exec CopyObject
@ObjectID = @ChildId,
@NewParentId = @NewId
fetch next from c into @ChildId
end
close c
deallocate c
But htis throws an error that the cursor already exists:
Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66
A cursor with the name 'c' already exists.
Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72
The cursor is already open.
I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.
Any ideas?
Thanks in advance,
Gert-Jan
View 3 Replies
View Related
Aug 25, 2015
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
View 2 Replies
View Related
Jul 23, 2005
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
View 4 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
           for select name, salary from ag
 open ll
            fetch from ll
Â
             while @@FETCH_STATUS=0
              fetch from ll
               update ag set salary=200 where 1=1
Â
  close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Sep 20, 2007
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
View 7 Replies
View Related
Sep 25, 2007
part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur
DEALLOCATE DBCur
Part 2
SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is,
how to join the part 1 n part 2?
is there posibility?
View 1 Replies
View Related
Feb 9, 2007
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
View 16 Replies
View Related
Jun 1, 2007
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.
View 2 Replies
View Related
Jun 11, 2004
Hi
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 ,
THANKS
View 3 Replies
View Related
May 12, 2006
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.
View 1 Replies
View Related
Oct 5, 2004
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors
declare Q cursor for
select systudentid from satrans
declare @id int
open Q
fetch next from Q into @id
while @@fetch_status = 0
begin
declare c cursor for
Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END
From SaTrans , systudent b where satrans.systudentid = b.systudentid
and satrans.systudentid = @id
declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money
set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
while @@fetch_status = 0
begin
set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount
insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id
end
close Q
deallocate Q
select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1
View 1 Replies
View Related
Jun 10, 2007
Hello,
I have the following tables:
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:
1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"
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.
View 1 Replies
View Related
Jan 18, 2008
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
View 2 Replies
View Related
Feb 26, 2008
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
View 12 Replies
View Related
Sep 21, 2004
I have a tough issue with a query.
I have the following structure
Table: Users
UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5
I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.
So if Param=1 it would return result set of 2,3,4,5,6
Anyone done this before or have any ideas?
Thanks,
ScAndal
View 1 Replies
View Related
Jul 20, 2005
Hi,
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.
To do that i'd have to have recursion query.
Thanks,
View 2 Replies
View Related
Oct 4, 2005
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
View 6 Replies
View Related
Dec 14, 2000
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
Best regards
Axel
View 2 Replies
View Related
Nov 24, 1999
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.
Thanks,
Jason
SQL DBA/MCSE
View 1 Replies
View Related
Dec 3, 2006
CREATE TABLE EMPLOYEE(
Initialschar(3)Not Null,
EmpNamechar(25)Not Null,
CellPhonechar(20),
ExperienceLevel char(12)Not Null,
SupervisorInitials char(3)
CONSTRAINTEmployeePKPRIMARY KEY(Initials),
CONSTRAINTExperienceLevelCK CHECK (ExperienceLevel in
('Junior', 'Senior', 'Master', 'SuperMaster')),
CONSTRAINT SupervisorFK FOREIGN KEY (SupervisorInitials)
REFERENCES EMPLOYEE (Initials)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
INSERT INTO EMPLOYEE VALUES ('JLM', 'Joe Morrone', '(603) 489-2245', 'Master', NULL)
INSERT INTO EMPLOYEE VALUES ('BNF', 'Brenda Fowler', '(603) 467-8894', 'SuperMaster', NULL)
INSERT INTO EMPLOYEE VALUES ('JG', 'John Gromek', '(603) 678-9987', 'Junior', 'BNF')
INSERT INTO EMPLOYEE VALUES ('BEK', 'Barbara Kimball', '(603) 884-2312', 'Master', 'BNF')
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.
View 3 Replies
View Related
Jun 5, 2002
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.
Appreciate any help.
Steve
View 1 Replies
View Related
Nov 1, 2007
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...
any ideas?
View 1 Replies
View Related
Feb 1, 2005
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)
RETURN
END
View 1 Replies
View Related
Jan 27, 2006
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!
View 3 Replies
View Related
Jul 13, 2004
I have a following structure
table A
userid ReferedBy
BA
C B
Table B
Userid compID
AAlpha
Bself
Cself
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
View 14 Replies
View Related
Sep 29, 2004
Hi,
i have the following table structure:
id | parentID
1 | NULL
2 | 1
3 | 2
4 | 3
im having trouble in creating a recursive query to return all the parents of a particular id. Have anyone ever done this?
thanks in advance,
View 7 Replies
View Related
Dec 23, 2004
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
Here is a quick schema:
Table: PRODUCT
--------------------------
PRODUCTIDPK
PRODUCTNAMEnVarChar(30)
Table: ASSEMBLY
--------------------------
PRODUCTIDPK(FK to PRODUCT.PRODUCTID)
COMPONENTIDPK(FK to PRODUCT.PRODUCTID)
QTYINT
I can write a query that takes the PRODUCTID, and returns all
PRODUCT
=======
PRODUCTIDPRODUCTNAME
--------------------
1Cage Assembly - Solid Sides
2Cage Assembly - Split Back
3Cage Assembly - Split Sides
4Cage Assembly - Split Top/Bottom
5Cage Assembly - Split Back and Sides
6Cage Assembly - Split Back and Top/Bottom
7Cage Assembly - Split Back and Sides and Top/Bottom
833S - Aluminum Divider
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
12Connector Socket
13Connector Screws
ASSEMBLY
=========
PRODUCTIDCOMPONENTQTY
---------------------
198
1104
1111
211
281
311
381
411
481
511
582
611
682
711
783
11128
11138
I need a query that will give me all parts for each PRODUCT.
Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"
The results would be:
PRODUCTIDPRODUCTNAME
--------------------
2Cage Assembly - Split Back
1Cage Assemble - Solid Back
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
833S - Aluminum Divider
12Connector Socket
13Connector Screws
Is it possible to write such a query or stored procedure?
View 4 Replies
View Related
Jan 7, 2004
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.
View 2 Replies
View Related
May 16, 2006
Hi,
Is there any recursive cursors in sqlserver?If any then what are recursive cursors?
Thanks in advance
View 3 Replies
View Related
