September 14, 2011 at 10:30 am
I can't wrap my head around using a with statement to recursively query.
here is what my data looks like:
customer, master customer
'customer2','customer1'
'customer3','customer2'
'customer4','customer3'
'customer9,'customer 27'
I am trying to create a view to get a result such as:
select * from wantedtable where customer = 'customer4'
results:
customer, recursionlevel
customer4, 0
customer3, 1
customer2, 2
customer1, 3
This data is a listing of customers and master customer for contract pricing. So customer 4 inherits the same contract pricing as customer 3 which inherits 2 and so on.
alternatively, a function to provide this like:
select * from functionname('customer1')
I have attempted a with statement
with thelist (CUSNO, master, inc)
AS
(
select cusno, master, 0 inc from DATA.dbo.CNTR_MST where CUSNO = '00197'
union all
select x.cusno, x.master, inc + 1 from DATA.dbo.CNTR_MST x
inner join thelist y on y.master = x.cusno
)
select * from thelist
select * from TIMSDATA.dbo.CNTR_MST where
CUSNO = '00197' or master = '00197'
The problem is that my output of
cusno, master
'00197', '05200', 0
'05200', 'CONTA', 1
is incomplete. The '05200' account has a master of 'CONTA' so my output needed to be
'00197', 0
'05200', 1
'CONTA', 2
(with inc meaning increment or level of recursion)
Any help would be greatly appreciated.
Thanks
September 14, 2011 at 11:22 am
Looks like you are fairly new around here. This is pretty hard to help because we don't have the ddl and sample data (insert statements) along with desired output based on the sample data. Please check out the link in my signature for the best practices on posting questions.
That being said it looks at a glance like the code you posted is correct. Whip up some ddl and sample data and we can have a go.
_______________________________________________________________
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/
September 14, 2011 at 11:40 am
if OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
go
create table #mytable
(
cusno char(5),
mastercustomer char(5)
)
go
insert into #mytable
select '00002','00001' UNION ALL
select '00003','00002' UNION ALL
select '00004','00003' UNION ALL
select '00009','00008' UNION ALL
select '00010','00009'
go
with thelist (CUSNO, mastercustomer, inc)
AS
(
select cusno, mastercustomer, 0 inc from #mytable where CUSNO = '00004'
union all
select x.cusno, x.mastercustomer, inc + 1 from #mytable x
inner join thelist y on y.mastercustomer = x.cusno
)
select * from thelist
this outputs 3 lines. CUSNO being the field I need. It is missing the final recursion which is the first entry in the temp table '00002','00001', so '00001' is not listed..
September 14, 2011 at 11:45 am
I should point out that I do realize that the CUSNO '00001' that is missing is only in the mastercustomer column so the inner join of y.mastercustomer = x.customer is what is filtering it out, I just don't know how to resolve that.
September 14, 2011 at 11:46 am
Thanks for the tables and data. 🙂
Your query is spot on correct and it returns exactly what it should. You said it should return a record for '0001' but that does not exist in the table.
_______________________________________________________________
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/
September 14, 2011 at 11:47 am
Looks like you posted just before I hit send. There is really no way to "resolve" it. Your query can't possibly return records that don't exist. There is no record in #MyTable with a cusno of '0001' so it will never be in the output.
_______________________________________________________________
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/
September 14, 2011 at 11:51 am
ok, can you see what I am after though? maybe I am both asking the wrong question and trying the wrong code.
in this scenario, the customer has a master account. You can see that this recurses up a few times. so CUSNO 00004 inherits 00003, which inherits 00002, which inherits 00001. I need to get a complete listing of every parent customer of 00004 which is 00003,00002, and 00001.
I am currently doing this wiht a few joins but with a limited number of allowed recursions, simply because I am stacking 5 joins limiting recursion to 5 levels. It is cumbersome and ugly and I am hoping to find a more elegant way and also learn something in the process.
September 14, 2011 at 12:01 pm
Yeah this is very common recursive cte and it looks like you have it spot on. From you sample data it is orphaned due to the lack of the '0001' row. Other than that this should do exactly what you are looking for. What is the max depth of your data? I can't remember off the top of my head but the max recursion before sql throws an error is something like 32,000. You can always use a maxrecursion query hint if you want to limit it when you select from thelist. That make sense?
_______________________________________________________________
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/
September 14, 2011 at 12:04 pm
SSCrazy, yes. I actually friend my brain too early today I think. I just realized that the mastercustomer column had the data I wanted not the CUSNO column. Missing the original CUSNO but I will union that in my function. Thanks for the help.
September 14, 2011 at 12:05 pm
You're welcome. glad you figured it out.
_______________________________________________________________
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply