Francisco Tapia
fhtapia at gmail.com
Thu Sep 23 19:55:46 CDT 2010
Damn you John... You always have to throw in the number of years you've been on this list as if that means anything... Nobody stops you from asking additional questions. Asger is polite and responds despite your rants... There are plenty of places on The net that address the problems with cursors... Another such post states http://weblogs.sqlteam.com/jeffs/archive/2008/06/05/sql-server-cursor-removal.aspx It's not just that a cursor taxes your system to yield poor performance...any piece of code can break due to unforeseen error handling, such an occurrence could render your production system unstable due to a cursor that simply will not close. Eating ram, and holding on to locks endlessly. This list is not all about you John, just like the OP whom probably need sot ensure not only results but also maintain an operating database as well as one that performs. Ask on this list about post you don't understand. How? Why? On Thursday, September 23, 2010, jwcolby <jwcolby at colbyconsulting.com> wrote: > > I didn't consider your first comment a polite request for explanation, rather a rant > > 1) A rant it was. I have work to do, I am not here to play silly games. Clues? I think not. > > >just reinforcing that you adhere to cursors. > > 2) I don't "adhere" to cursors, I use the tools I know and can understand. I have probably used a > cursor a handful of times over the years. This KIND of things is exactly where I would use it, > getting information from a different row to use in the current row. > > If you will teach me a better tool, I will most certainly use that instead. But your example (sans > explanation) did not teach me anything. > > > Second, step-by-step explanation of code examples may be fine for a class room, but IMO would be > totally inadequate for a list like this. > > And why is that. I have been on this and the Access and VB lists for 15 years and we do give step > by step explanations of code. Why would you not? This *is* a classroom. Why do you think people > come here? > > BTW, just an FYI, a year or two ago (over on the Access list) I wrote an entire series of emails on > Access classes and withevents. It is a complex subject and needed more than an example. And I > never once told the reader to go RTFM. I put my money where my mouth is. > > >If someone does not understand the example or some parts of the example then I - and I'm sure most > listers - will readily give an answer if asked politely. > > <rant mode> > > I was not being rude, I was saying what I needed to say. I repeat, if I understood the example, > then I wouldn't need you to write the example. By definition if you have to provide me an example > then you need to explain it. Or if you know of examples on the internet that explain it provide those. > > Look at any book on technical things like SQL or C#. They don't just throw out examples, they > discuss the principals, then they provide examples, then they explain how the example applies to the > principal. > > Asger, I absolutely appreciate your taking the time. I really do. What I *don't* appreciate is the > "Read the BOL" attitude. I probably own as many books as anyone on this list, SHELVES full of them. > But I work for a living, and my job is NOT as a DBA. The DBA is about 5% of my job. I am a sole > proprietor who routinely uses, in any given day, SQL Server, C# and Access. I build my own > computers, install software, and everything else you can think of that has to be done to keep my > business running, *just me* doing the whole damned thing. I don't have time to solve "clues". > > I need as much help as you can give so that I can learn it from you and your examples *quickly*. If > you don't want to provide that help, just say so, I can understand that. Whatever you do, *don't* > say "here's an example, now go figure it out". > > SHITCAN! > > <\rant mode> > > And Asger, thank you very much for the detailed explanation. I will study that and understand that. > > > John W. Colby > www.ColbyConsulting.com > > On 9/23/2010 7:25 PM, Asger Blond wrote: >> John, >> To get you a clue what the code does, and what I did. >> First, when answering a question I normally make effort recreating the scenario in question. Which was what I did in this case: created a table and a query with the data provided by Paul. If you have done the same you can easily test and play around with the code example - soon you should notice what's going on. >> Second, step-by-step explanation of code examples may be fine for a class room, but IMO would be totally inadequate for a list like this. If someone does not understand the example or some parts of the example then I - and I'm sure most listers - will readily give an answer if asked politely. >> I didn't consider your first comment a polite request for explanation, rather a rant just reinforcing that you adhere to cursors. >> Being more reasonable in your second reply I'll supply explanation using the example from my second posting to Paul: >> >> WITH cte (RowNumber, PersonID, StartCode, EndCode) AS (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PersonID), PersonID, StartCode, EndCode FROM YourQuery) UPDATE cte SET StartCode = (SELECT EndCode FROM cte AS cte2 WHERE cte.RowNumber = cte2.RowNumber+1 AND cte.PersonID = cte2.PersonID) WHERE RowNumber> 1 >> >> The first part - before UPDATE - creates a Common Table Expression which you know is a kind of virtual table that can be used instead of a temporary table. In this cte I make use of the ranking function ROW_NUMBER() with the qualification PARTION BY PersonID ORDER BY PersonID to get a numbering sequence for each group (or "partition") of PersonID's. >> Suppose you have the following rows in the query, where I have deliberately added two more rows to Pauls query for the purpose of showing different PersonID's: >> >> PersonID Date StartCode EndCode >> 111111 29/09/2010 AA1 1AA BB1 1BB >> 111111 29/09/2010 AA2 2AA BB2 2BB >> 111111 29/09/2010 AA3 3AA BB3 3BB >> 111111 29/09/2010 AA4 4AA BB4 4BB >> 222222 29/09/2010 XX1 1XX YY1 1YY >> 222222 29/09/2010 XX2 2XX YY2 1YY >> >> With this recordset you can query the supplied cte using: >> WITH cte (RowNumber, PersonID, StartCode, EndCode) AS (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY PersonID), PersonID, StartCode, EndCode FROM YourQuery) SELECT * FROM cte >> The result will be: >> >> RowNumber StartCode EndCode >> 1 AA1 1AA BB1 1BB >> 2 AA2 2AA BB2 2BB >> 3 AA3 3AA BB3 3BB >> 4 AA4 4AA BB4 4BB >> 1 XX1 1XX YY1 1YY >> 2 XX2 2XX YY2 2YY >> >> Now the last UPDATE part of the code use a correlated subquery that selects the EndCode of the previous row inside each PersonID and use it to replace the value of StartCode. It does this for all rows except for rows where RowNumber = 1. If this last where clause is omitted then all rows with RowNumber = 1 will get a NULL as StartCode. This might actually be wanted and can easily be achieved omitting the clause. >> Running the code as is will give the following records: >> >> PersonID Date StartCode EndCode >> 11111 29/09/2010 AA1 1AA BB1 1BB >> 11111 29/09/2010 BB1 1BB BB2 2BB >> 11111 29/09/2010 BB2 2BB BB3 3BB >> 11111 29/09/2010 BB3 3BB BB4 4BB >> 22222 29/09/2010 XX1 1XX YY1 1YY >> 22222 29/09/2010 YY1 1YY YY2 2YY >> >> As I read Paul's question this is what's wanted. >> >> PS: If an operation can be achieved both by a cu -- -Francisco http://bit.ly/sqlthis | Tsql and More...