Home » SQL & PL/SQL » SQL & PL/SQL » Noobish question on looping within a cursor (nested loops?)
Noobish question on looping within a cursor (nested loops?) [message #189709] Fri, 25 August 2006 14:30 Go to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Howdy folks, back again. Google has once again succeeded in almost helping me, so I'd like to run it by you guys anyway. I think this question has to do with looping within a cursor.

Most of the cursors I have worked with have been fairly simple; the cursor selects a user ID, and then I loop through one user at a time and perform my work.

This time, I need to do something a little more complex. I have a certain set of users, a set of requisitions, and there is a table that stores "actions." The action table includes the user id, the requisition id, and an action code. So, "User A took Action X regarding Requistion F."

For each user in my set of criteria, I need to look through the action table, and for each row found, write certain data to a database table.

At this point, my cursor brings back a list of all the user ids I need to check for. But, in the actions table, each user id could have multiple rows. How do I tell the code to loop through this subset of rows? I've tried this a few different ways and not had much success (including one fun endless loop Razz ).

For example, the data in the action table might look like this:
User_ID  Req_ID  Action
12345    99999X  Buy
12345    99999X  Sell
12345    99999X  Trade
12345    33333X  Trade
98765    37585P  Sell


My cursor (right now) simply brings back a list of users I need to check for, like this:

cursor mycursor is
select user_id
from user_table
where user_id in (12345, 98765)


So, when I run the program, it looks in the cursor, and grabs user_id 12345. It goes to the action table, and sees the first row there matches, and grabs that transaction.

At that point, how can I tell it to keep going back until there are no more 12345s, before proceeding to the next user_id? Should I be modifying my cursor to only return a unique combination from the action table?

I know this is probably relatively easy, but I think I've just been looking at it for too long. Razz

Thanks very much all,

Steve

[Updated on: Fri, 25 August 2006 14:31]

Report message to a moderator

Re: Noobish question on looping within a cursor (nested loops?) [message #189716 is a reply to message #189709] Fri, 25 August 2006 17:24 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I often find it frustrating to try to newbie question because several newbies so abridge the question, I guess in an attempt to make it easier to understand, that it either makes no sense or seems to simple. So if I missed some complexity, I tried to do the best I could from your problem statement. As you describe, you have a very simple problem. Just use this as your cursor. You won't have to "go back" for anything. It will give you all of the rows you need. Note, however, that you actually don't need to join to the reqs or users table in this query. I only included them on the assumption that you need some info from columns that aren't in action.

SELECT a.user_id
      ,a.req_id
      ,a.action
FROM action a
    ,reqs   r
    ,users  u
WHERE a.user_id IN (12345, 98765)
  AND r.req_id = a.req_id
  AND u.user_id = a.user_id
Re: Noobish question on looping within a cursor (nested loops?) [message #189798 is a reply to message #189716] Sun, 27 August 2006 20:28 Go to previous message
sbattisti
Messages: 39
Registered: June 2005
Member
Scott,

Thanks very much for your help on this, things are working great with the cursor configured as you suggested.

Cheers!

Steve
Previous Topic: diffrent between a prameter
Next Topic: Settings before Login
Goto Forum:
  


Current Time: Fri Dec 02 12:31:01 CST 2016

Total time taken to generate the page: 0.08191 seconds