Number rows incrementally based on previous row

[mwolf00]

I have a table that records all events (events). Some of those events causes data to be written to a second table (returnreason) where we have a column that counts the iteration as the event may happen more than once. Unfortunately I cannot find a way to link the particular event to the iteration (1,2,3,etc.). I thought that if I could add an incremental column to the events, then I could use that to link to the iteration.

Event

FOLDERID EVENTID
123 7
123 12
123 17


ReturnReason

FOLDERID ITERATION REASONCODE
123 1 A
123 2 B
123 2 A
123 3 D


For instance, I need the query to tie eventid 12 to both iteration 2 results.

I have tried using LAG to compare the two rows to increase the value but I can't seem to get it to work. I am just trying to get the query to return the folderid and the number 1 for the first time it appears and the number 2 for the second time it appears and so on. I do not know if I can use that the give me the results I need in the end.

select e.efolderid, e.eeventid, 
  case when lag(efolderid,1,0) over (order by efolderid) =  e.efolderid then 
    lag(testCol,1,0) over (order by efolderid) + 1 else 1 end testCol
from eevent e
WHERE e.EACTIONNAME = 'Can Not Accept'
   AND e.EFROMSTAGE = 'QA Non Acceptance Review'

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[kutz13]

If the list of events is small, you might consider a before insert trigger on the first table which calls the nextval in a sequence. You may have a sequence for each event and wrap things up in a function which is called by the trigger.

The difference between eggs and bacon: The chickens involved but the pigs committed

 

[carp]

Your returnreason table should have a primary key (e.g., returnreason_id). If it doesn't, than I would suggest that is the column you should be concerned with adding. Then, with the addition of a returnreasonid column on your event table, you could establish a foreign key dependency and link events to returns.

 

[mwolf00]

Thank you both for the responses. Unfortunately, the events table is part of a commercially sold software program and should not be modified (it currently has about 4.5M rows). Also the returnReason table has 60k rows and while I can add a column to it, I'm not sure how to populate that column to hold the correct eventID for past transactions. The solution may just be to add the column and populate it from here forward. Unfortunately, the programmer who designed the table did not consider how we were going to report on this...

Thanks again!

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[carp]

Aha! So-you're cleaning up somebody else's mess, but can only fix half of it. OK, it's becoming clearer. Do either (hopefully both) of the tables have an event date column? Also, how do you determine if an even actually spawned a row in the ReturnReason table?

 

[mwolf00]

Carp,

Thanks for the replies. I think I found a solution by partitioning the row number on the folderid...

SELECT m.
(select rr.efolderid, rr.return_reason , 'sub' src, rr.return_date
FROM rbm_treturnreason rr
JOIN eevent e on rr.efolderid = e.efolderid 
	AND e.eactionname = 'Can Not Accept' 
	and e.efromstage = 'Submitter Review' 
	and e.eeventtime = (select min(eeventtime) 
		from eevent 
		where eeventtime >= rr.return_date 
		and eactionname = 'Can Not Accept'
		and efolderid = rr.efolderid)
where e.eeventtime BETWEEN
    (TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -1),'MM')|| '16' || TO_CHAR(ADD_MONTHS(SYSDATE, -1),'YYYY'),'MM/DD/YYYY'))
   AND (TO_DATE(TO_CHAR(SYSDATE,'MM') || '16' || TO_CHAR(SYSDATE,'YYYY'),'MM/DD/YYYY'))				   
UNION ALL			   
select rr.efolderid, rr.return_reason, 'con' src, dt.eeventtime return_date
FROM rbm_treturnreason rr 
JOIN 			   
(select efolderid, eeventtime
				, [red]ROW_NUMBER( ) OVER (PARTITION BY
				efolderid ORDER BY eeventtime
				NULLS LAST) itNum[/red]
			   from eevent e
			   where e.eactionname = 'Can Not Accept' 
				and e.efromstage = 'QA Non Acceptance Review'
				and e.eeventtime BETWEEN
    (TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -1),'MM')|| '16' || TO_CHAR(ADD_MONTHS(SYSDATE, -1),'YYYY'),'MM/DD/YYYY'))
   AND (TO_DATE(TO_CHAR(SYSDATE,'MM') || '16' || TO_CHAR(SYSDATE,'YYYY'),'MM/DD/YYYY'))) dt	on dt.efolderid = rr.efolderid and [red]dt.itnum = rr.rejection_iteration)[/red]

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook