Incorrect Cursor Output

Question

Hi all, 

I'm trying to create a cursor within a cursor for the following data(small test sample similar to actual data):

patid eventt dischstat flag
001   inpat  AR           NULL
001   inpat  AR           NULL
001   inpat  RO          NULL
001   otpat  OD          NULL
002   inpat  RA          NULL
002   OTpat  OD          NULL
003   INpat  NV          NULL
003   INpat  NV          NULL
003   OTpat  NV         NULL
003   OTpat  RA         NULL         

This is the code I'm using: 

use test
declare @patid char(3);
declare @cursor2 char(2);
declare PATID cursor for select patid from PPANALYSIS;
open PATID
fetch next from PATID into @PATID
	while @@FETCH_STATUS = 0
	begin
		declare cursor2 scroll cursor for 
				select DISCHSTAT from ppanalysis where patid=@patid;
		open cursor2;
		fetch next from cursor2 into @cursor2;
			while @@FETCH_STATUS = 0
			begin
			if @cursor2='OD' 
					fetch last from cursor2 into @cursor2;
							if @cursor2 ='AR' 
								print 'pat2'
							else if @cursor2 = 'RO' 
								print 'pat1'
							else print 'pat3'
				
				fetch next from cursor2 into @cursor2;
			end
		close cursor2
		deallocate cursor2
		fetch next from patid into @patid
end
close patid
deallocate patid

The output isn't what I was expecting. 

I get this: 

pat2
pat2
pat1
pat3
pat2
pat2
pat1
pat3
pat2
pat2
pat1
pat3
pat2
pat2
pat1
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3
pat3

I know I'm close here, but I can't seem to figure out what this last hump is attributable to. I need this to work because eventually I will use it to udpate the flag field based on the previous values of dischstat for a given patid. 

Thank you!

Answer 1

Silly me but I can't see what the problem is here?

Answer 2

To answer this question we would need to know what you wanted as the output.

One thing is that you do a FETCH LAST, but your DECLARE statement for the cursor does not have an ORDER BY, which means you do not know when row you will get when you do a FETCH LAST.

But my real recommendation is that you tell us what you really want to do in the end (that is the rules for whatever update you intend to do) and show us what the table would look like after the update.  Almost certainly we will be able to show you how to do this without a cursor.  Cursor's are very inefficient and a cursor embedded inside another cursor is extremely inefficient.  There are cases where a cursor is the best solution, but they are very, very rare.

Tom

Answer 3

So the output I really want is just:

pat1

pat2

pat3

basically, for cases where there is an OD dischstat, I want to look at the previous row and see what that dischstat was.

If it's RO, like patid1m then I want it to keep looking back until it's AR and set flag='Y'. 

For pat2, it's RA in the prev. column before the OD, so I want the flag='Y'.

For pat3, there is no OD in dischstat so I want it to set flag='N'

I don't think the cursor is stopping when it should. I want it to go straight to the first instance of OD for each patid. Then, I want it to look backwards until a particular dischstat is present and flag it meaning each patid should really only have 1 output from looking at it's data. 

Since the location of where OD can be present, and the number of dischstats can vary by patid, I can't do a simple case-when statement. 

Answer 4

As soon as you said the word PREVIOUS in your reply you are saying that there is an order but I see no ORDER BY in your SELECT statements for your cursors. So as Tom Cooper says there should at least be an ORDER BY clause.