| Author |
Topic |
|
Samadhi69
Starting Member
20 Posts |
 Posted - 2009-05-08 : 13:22:19
|
| I've used loops and recursion in other languages but am not 100% familiar with how to do it in SQL.If someone could give me the bare bones syntax for the following, I'd be most grateful.From one table select a set of IDs based on a criteria.For each ID select a set of sub IDs and do stuff.I'm assuming I have to do use a select statement to create a set and loop through the set, but I'm not sure what to create.If con is the opposite of pro, what's the opposite of progress? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-08 : 13:24:19
|
| you dont need loop for above scenario. all you need is a join between two tables to get ids and subids. set based approach is better here. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 13:25:43
|
Maybe there is no need to do some stuff based on a loop through a recordset.Show table structure, sample data and wanted output please.Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Samadhi69
Starting Member
20 Posts |
Posted - 2009-05-08 : 14:22:06
|
| The "stuff" is a rather complex set of rules applied to the steps. I need to be able to analyze them sequentially to apply these various rules.For example suppose (this is not the case but suppose) the first is a customer database, the second is a list of any activity. Say you want to flag who is delinquent in payment. Easy answer would be 30 days since last payment. But if there's an open repair work order on their house then it's reset to the date that it's closed. If a new order of some other type opens then it's from the date that one was opened (not closed), if there is etc etc etc.If there's a recursive way to do this I'd love it (some orders can be opened ad infinitum).Not sure if that made any sense.If con is the opposite of pro, what's the opposite of progress? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-08 : 14:23:59
|
| not fully...so what all do you need to determine recursively? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 14:30:55
|
Sounds not like impossible to do it set based.In face of many conditions it is possible to flag who is delinquent in payment.OK - maybe, maybe you have to use a few statements in the right order to do the job - but it is possible.In most cases it is the better (faster) way...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-05-08 : 14:32:31
|
You'd be amazed how the most complex stuff (normally thought of as needing a row by row precedural approach) can be accomplished using set-based methods. If you post a specific requirement we'll give you an example.But to answer your question, the simplest looping is done using the WHILE keyword.while <boolean expression>begin--do stuffend--here is a real example of iterating through a result set:declare @id intselect @id = min(id) from sysobjects where id < 10while @id is not nullbegin select object_name(@id) select @id = min(id) from sysobjects where id < 10 and id > @idend EDIT:forgot to include the WHILE  Be One with the OptimizerTG |
|
|
|
Samadhi69
Starting Member
20 Posts |
Posted - 2009-05-08 : 14:40:50
|
| In the above example, the date to base delinquency on. The method I've been attempting is to have a table with the IDs and the "status" and the date from which to determine that from. IMO this should already have been done (IE doing a certain thing resets those versus trying to find those things after the fact), but I didn't design it I work for a third party accounting firm.I was thinking recursion because you can recursively eliminate things that are "closed" until your reach what it's current status should be. And I say recursively because there may be 10, 20 or more steps that affect the status, depending on what the status was when it was opened.Looping through the associated transactions and applying if thens seemed the simplest (if sloooooow) way to do it.If con is the opposite of pro, what's the opposite of progress? |
|
|
|
Samadhi69
Starting Member
20 Posts |
Posted - 2009-05-08 : 14:44:38
|
| TG: Thanks, that helps a lot. I couldn't think of a good way to iterate through the set (my old professors have turned their faces from me in shame).If con is the opposite of pro, what's the opposite of progress? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-08 : 15:20:37
|
| Another technique you can use for complex logic: if the statement is very difficult to express, see if the logical inverse is easier to define. i.e., if finding the non-deliquents is complicated, assume everyone is non-deliquent and then mark the ones that are delinquent. It can be easier because the inverse logic is simpler, you can evaluate each condition one at a time, and have each statement ignore rows that are already marked. It also doesn't require loops or recursion. |
|
|
|
Samadhi69
Starting Member
20 Posts |
Posted - 2009-05-08 : 16:06:50
|
| Good idea. The conjugate of a boolean expression should always be the same order of complexity, but the gathering of the data behind them can be hugely different when practically applied. I'll write them out and see if anything jumps out at me.ThanksIf con is the opposite of pro, what's the opposite of progress? |
|
|
|
|
While loop noob question