Lets say I have a cursor and I am passing a Purchase Order number and it returns a purchase order with 100 items. Now I use FOR function to step through those 100 items in the cursor. Now lets suppose I have a legitimate reason to jump immediately to the 90th item in the list. Rather than step through the FOR / END FOR loop 90 times is there any way to automatically jump to the 90th item? My guess from what I've read in documentation is NO but I thought I would ask anyway.
Hi John, I think I understand what you're suggesting in theory, however I still think I would have the same problem unless I'm misinterpreting what you're saying. That is I think using your concept I would have to pass a PO number and rank to my cursor and then if I wanted to go immediately to another different ranked row I would have to close the cursor and then re-open passing again a PO and new rank. Am I right?
Just to elaborate a little bit, my real problem is actually that I have a cursor within another cursor loop;
I'm stepping through a list of purchase orders in table A and for each row of table A I'm performing a secondary lookup (if you will) to table B (using another cursor) passing the purchase order number. Table B has a large volume of PO's and it seems for each row in table A I have to open and close the second cursor having to again sift through large volume of records each time. I was thinking if I could just keep the 2nd cursor open and jump to the next PO (perhaps thousands of records below) it would be much more efficient.
PS: John in my above multi cursor scenario I declare my cursors a single time at the beginning of my procedure.
As I step through each record of the first cursor and then perform a FOR loop on the second cursor, my guess is it's as if I'm querying the entire dataset declared in the second cursor each and every time. This was an assumption on my end because the performance was so horrendous. If you perform a FOR loop against a cursor (each time passing a key field such as PO) is it in affect re-running the declared cursor each and every time or is it just jumping to the passed values from the single original cursor?
Ahhh yes Lars, I was waiting for you to ask this question. The answer is because there is not entirely matching key fields to join between the two tables or I would indeed do this. The join between the two tables would return too many results from the second table and so I have to step through the results of the second table, incrementally count quantities until a certain quantity is met and then stop and loop to next record in first cursor. For next record of first cursor I have to make sure the same records from the second cursor are not processed a second time. And this is just a piece of the complex logic, there are a few different scenarios and ways of flagging records that need to happen making the join not feasible.
Thanks for answering my question though, that's what I needed to know.
One other possible approach given your current logic would be to index into a result set, say at position 90, by doing SELECT .. FROM .. WHERE .. OFFSET 90 (and if needed, LIMIT if you need a subset).
Hard to say what the performance impact would be, but generally speaking looping with single selects carries more overhead than looping through a cursor. But perhaps it's a useful approach?
Regarding your logic, it may be too much trouble - but if not, I'd be interested to hear what all is required.
If you have to look for a certain count, could you not do something like:
SELECT "JOIN_FIELDS", COUNT(*).. FROM.. GROUP BY.. HAVING COUNT(*) > 5 (as example)
and then join those results?
I've seen a lot of creative ways to solve complex logic with set-processing approaches. Perhaps there's more room for optimization and simplification?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.