Query for Recursive Data Access

Question

Query for Recursive Data Access

saurabh.kumar

SSC Enthusiast

Points: 151
More actions
June 25, 2011 at 3:34 am

#104075

I am attaching the table structure here as a doc. I am looking for a query that takes in WOId as an input and returns all the prevWOIds records. I mean to say that the result set shall return the rows that have history data. But problem I am facing is that for the first entry in the table, the prevWOId is NULL.
There is a self foreign key constraint on the table 's WOID column. As a result I am unable to insert any non null value in the prevWOId column in the first row.
Can someone help me with the query. Please find the expected result set and the table structure in the file attached.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the first link in my signature on how to provide sample data.

In your Excel file the data types per column are missing as well as such simple things like a table name. The data are not ready to use, so we'd have to invest the time to setup a test scenario instead of immediately start working on a solution.

Also, please clarify what the PrevWOId column refers to. There is no other numeric field in the table.

You could also have a look at BOL (BooksOnLine, the SQL Server help system usually installed together with SQL Server), section "recursive CTEs".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

saurabh.kumar SSC Enthusiast Points: 151 More actions · Answer 2

Sorry for the missing info. FYI all 3 columns are nvarchar(max). Table name is WODetails.

I am re attaching the sheet with updated info.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Seems like you didn't really found the time to read the article I pointed you at...

The data are still not ready to use.

I'd also question the concept of having all columns being varchar(max). Do you really expect the value in WOId to everexceed 8000 character? Or even 100? Or 20? I strongly recommend to fix the design.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

saurabh.kumar SSC Enthusiast Points: 151 More actions · Answer 4

I was able to find a solution for the problem and I am posting the same for the benefit of others:

http://msdn.microsoft.com/en-us/library/ms175972%28SQL.90%29.aspx

Look under section J of the article. This shall help you write a recursive CTE .

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Thanks for posting the feedback for the solution you found.

As a side bar, it's a real shame that you didn't post data using the method that Lutz suggested... it's likely that someone would have taken more interest in your post and probably would have posted a tested solution.

Consider posting some readily consumable data in a manner outlined in the article Lutz provided the link for, for your next post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)