Need help to build recursive query

Question

Need help to build recursive query

squvi.87

Default port

Points: 1450
More actions
September 14, 2015 at 6:35 am

#307621

Hi There,
Sample Data :
create table #sample (Mainstream int, Substream1 int , substream2 int , [status] varchar(10))
Insert into #sample values (1,2,4,'Valid')
Insert into #sample values (2,6,3,'Valid')
Insert into #sample values (3,2,1,'Valid')
Insert into #sample values (4,1,null,'InValid')
Insert into #sample values (5,6,null,'Valid')
Insert into #sample values (6,null,null,'Valid')
Above is the sample data.
This is self referencing table. Mainstream column is based on the sub streams.
If any of the sub stream fails then corresponding main stream also fail.
I will be available with the status based on main stream. I have to find the current status based on the sub stream's status.
How can I handle this ????
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

squvi.87 (9/14/2015)
Hi There,
Above is the sample data.
This is self referencing table. Mainstream column is based on the sub streams. If any of the sub stream fails then corresponding main stream also fail.
I will be available with the status based on main stream. I have to update the status based on the sub stream's status.
Here,
1. mainstream 4 is invalid, and 4 is used as substream in mainstream1. So need to update mainstream 1 also fail.
2. now mainstream 1 is invalid, it is used as substream in 3,4. so both status should be updated as invalid
Now based on 3 status should be updated. So and so.
How can I handle this ????

Your question has received a lot of views but no responses. That is because you didn't post a very clear question. Your sample data has two columns with the same name and it is not very clear what you are trying to do here. Please take a few minutes and read the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

squvi.87 Default port Points: 1450 More actions · Answer 2

Sean Lange (9/14/2015)
Your question has received a lot of views but no responses. That is because you didn't post a very clear question. Your sample data has two columns with the same name and it is not very clear what you are trying to do here. Please take a few minutes and read the first link in my signature for best practices when posting questions.

Thanks for the suggestion. I have edited my question. Am I make my question understood ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

squvi.87 (9/14/2015)
Sean Lange (9/14/2015)
Your question has received a lot of views but no responses. That is because you didn't post a very clear question. Your sample data has two columns with the same name and it is not very clear what you are trying to do here. Please take a few minutes and read the first link in my signature for best practices when posting questions.
Thanks for the suggestion. I have edited my question. Am I make my question understood ?

So you can reference it by either substream1 or substream2? Regardless this is pretty much a typical recursive cte. There are thousands and thousands of examples of how to do this in every corner of the internet. See if you can research it a little and post back what you have tried. Sure I could write this for you but you will learn a lot more if you figure it out on your own.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

squvi.87 Default port Points: 1450 More actions · Answer 4

So you can reference it by either substream1 or substream2? Regardless this is pretty much a typical recursive cte. There are thousands and thousands of examples of how to do this in every corner of the internet. See if you can research it a little and post back what you have tried. Sure I could write this for you but you will learn a lot more if you figure it out on your own.

CREATE TABLE #Table ( MAIN INT,SUB1 INT,SUB2 INT,[STATUS] VARCHAR(10),MODIFIED VARCHAR(10))

INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(1,2,4,'Valid')

INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(2,4,5,'Valid')

INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(3,6,4,'Valid')

INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(4,2,1,'Valid')

INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(5,3,4,'Valid')

INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(6,'','','InValid')

SELECT * FROM #Table

DROP TABLE #Table

DECLARE @count INT = 0;

DECLARE @row INT;

SET @row = (SELECT COUNT(*) FROM #Table)

PRINT @row

WHILE @count < @row

BEGIN

UPDATE te SET te.MODIFIED = 'InValid' FROM #Table te WHERE te.main IN (

SELECT t.main

FROM #Table t

WHERE t.sub1 IN (select t1.main from #Table t1 WHERE t1.[STATUS] LIKE 'Invalid' OR t1.[MODIFIED] LIKE 'InValid')

OR t.sub2 IN (select t1.main from #Table t1 WHERE t1.[STATUS] LIKE 'Invalid' OR t1.[MODIFIED] LIKE 'InValid') )

SET @count = @count + 1

END;

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

squvi.87 (9/15/2015)
So you can reference it by either substream1 or substream2? Regardless this is pretty much a typical recursive cte. There are thousands and thousands of examples of how to do this in every corner of the internet. See if you can research it a little and post back what you have tried. Sure I could write this for you but you will learn a lot more if you figure it out on your own.
CREATE TABLE #Table ( MAIN INT,SUB1 INT,SUB2 INT,[STATUS] VARCHAR(10),MODIFIED VARCHAR(10))
INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(1,2,4,'Valid')
INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(2,4,5,'Valid')
INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(3,6,4,'Valid')
INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(4,2,1,'Valid')
INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(5,3,4,'Valid')
INSERT iNTO #Table(MAIN,SUB1,SUB2,[STATUS]) VALUES(6,'','','InValid')
SELECT * FROM #Table
DROP TABLE #Table
DECLARE @count INT = 0;
DECLARE @row INT;
SET @row = (SELECT COUNT(*) FROM #Table)
PRINT @row
WHILE @count < @row
BEGIN
UPDATE te SET te.MODIFIED = 'InValid' FROM #Table te WHERE te.main IN (
SELECT t.main
FROM #Table t
WHERE t.sub1 IN (select t1.main from #Table t1 WHERE t1.[STATUS] LIKE 'Invalid' OR t1.[MODIFIED] LIKE 'InValid')
OR t.sub2 IN (select t1.main from #Table t1 WHERE t1.[STATUS] LIKE 'Invalid' OR t1.[MODIFIED] LIKE 'InValid') )
SET @count = @count + 1
END;

This new set of data is not the same as the one you posted earlier. Is this giving you the correct results? If not, what should they be from this sample data?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

squvi.87 Default port Points: 1450 More actions · Answer 6

This new set of data is not the same as the one you posted earlier. Is this giving you the correct results? If not, what should they be from this sample data?

Yes this new set of data, query gives me correct result. is there any other way to do that ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

squvi.87 (9/15/2015)
This new set of data is not the same as the one you posted earlier. Is this giving you the correct results? If not, what should they be from this sample data?
Yes this new set of data, query gives me correct result. is there any other way to do that ?

Yes with a recursive cte. I might not perform any better but it sure would be less complicated than the while loop you posted. I had to read that about a dozen times before I understood what was happening there.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

You can simplify your query like this:

IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP TABLE #Table;

CREATE TABLE #Table ( MAIN INT,SUB1 INT,SUB2 INT,[STATUS] VARCHAR(10),MODIFIED VARCHAR(10))

INSERT INTO #Table(MAIN,SUB1,SUB2,[STATUS])

VALUES (1,2,4,'Valid'),(2,4,5,'Valid'),(3,6,4,'Valid'),(4,2,1,'Valid'),(5,3,4,'Valid'),(6,0,0,'Invalid');

DECLARE @count INT = 0, @row INT = (SELECT COUNT(*) FROM #Table);

WHILE @count < @row

BEGIN

UPDATE te SET te.MODIFIED = 'InValid'

FROM #Table te

WHERE EXISTS

(

SELECT t1.main

FROM #Table t1

WHERE (t1.main IN (te.sub1, te.sub2) AND 'Invalid' IN (t1.[STATUS], t1.[MODIFIED]))

)

SET @count += 1;

END;

SELECT * FROM #Table;

Edit: removed bad and statement in EXISTS subquery

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

You can also improve on that a bit Alan, it won't scale at all well (try it for a sample of 700 rows)

I would always try and keep a WHILE loop down to one statement, no BEGIN...END blocks, and no variables.

select 'Started',getdate(); -- get the party started - so @@rowcount is not zero

WHILE @@rowcount>0

UPDATE te SET te.MODIFIED = 'InValid'

FROM #Table te

JOIN #Table te2

ON (te2.main IN (te.sub1, te.sub2) AND 'Invalid' IN (te2.[STATUS], te2.[MODIFIED]))

WHERE te.MODIFIED is null;

--option(recompile); /* might help with some data distributions but adds overhead to every iteration */

And for anything more than a few hundred rows, I would add these indexes to support that:

create index #ix_1 on #table(MAIN) include(STATUS,MODIFIED);

create index #ix_2 on #table(MODIFIED) include(SUB1,SUB2) WHERE MODIFIED IS NULL;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

mister.magoo (9/15/2015)
You can also improve on that a bit Alan, it won't scale at all well (try it for a sample of 700 rows)
I would always try and keep a WHILE loop down to one statement, no BEGIN...END blocks, and no variables.
select 'Started',getdate(); -- get the party started - so @@rowcount is not zero
WHILE @@rowcount>0
UPDATE te SET te.MODIFIED = 'InValid'
FROM #Table te
JOIN #Table te2
ON (te2.main IN (te.sub1, te.sub2) AND 'Invalid' IN (te2.[STATUS], te2.[MODIFIED]))
WHERE te.MODIFIED is null;
--option(recompile); /* might help with some data distributions but adds overhead to every iteration */
And for anything more than a few hundred rows, I would add these indexes to support that:
create index #ix_1 on #table(MAIN) include(STATUS,MODIFIED);
create index #ix_2 on #table(MODIFIED) include(SUB1,SUB2) WHERE MODIFIED IS NULL;

Thank you sir.

I was unhappy with my solution because I was certain it could be done without a loop and, while trying to come up a set-based solution, I realized that the OP's solution does not make any sense to me. I changed up the sample data to be more meaningful and created a solution that I believe with produce that correct results.

Here's what I think is supposed to happen:

... and my solution.

IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP TABLE #Table;

CREATE TABLE #Table ( MAIN INT,SUB1 INT,SUB2 INT,[STATUS] VARCHAR(10),MODIFIED VARCHAR(10))

INSERT INTO #Table(MAIN,SUB1,SUB2,[STATUS])

VALUES (1,2,4,'Valid'),(2,4,5,'Invalid'),(3,6,4,'Invalid'),(4,2,1,'Valid'),(5,3,4,'Valid'),(6,0,0,'Invalid');

-- before

SELECT * FROM #Table;

WITH UpdateME AS

(

SELECT *

FROM #Table te

WHERE EXISTS

(

SELECT t1.main

FROM #Table t1

WHERE (t1.main IN (te.sub1, te.sub2))

)

AND 'Invalid' IN (te.[STATUS],te.MODIFIED)

)

UPDATE UpdateMe SET MODIFIED = 'Invalid'

-- after

SELECT * FROM #Table;

I think this is what the OP needs (or something very close).

+1 on the indexes. 😀

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

squvi.87 Default port Points: 1450 More actions · Answer 11

So you can reference it by either substream1 or substream2? Regardless this is pretty much a typical recursive cte. There are thousands and thousands of examples of how to do this in every corner of the internet. See if you can research it a little and post back what you have tried. Sure I could write this for you but you will learn a lot more if you figure it out on your own.

im trying with recursive cte .

but stuck up with below level of code. Recursion is keep going..how to handle this?

;with cte as (

select *,'invalid'as newstatus from #Table where status = 'Invalid'

union all

select b.* ,'invalid'as newstatus from #Table b JOin CTE a On a.main in (b.SUB1 , b.SUB2) and b.STATUS = 'Valid' and a.newstatus ='Invalid' and b.MAIN not in (a.MAIN)

)

select *From cte