how to find the 4th order for each customer with cursor

Question

0.00/5 (No votes)

See more:

I want to find the 4-th order for each customer and his Title_order (ordering by date of order) between all of its orders. If they dont have orders or have less than 4 return NULL. It's restricted using Cursor. I have created this but returns me all the order.
I have the tables
customer with filds fname, lname,Id_customer
order with filds ID_order, date, title_order

SQL

ALTER function [dbo].[f4order](@id_customer int, @ID_order int)
returns varchar (255)
as begin 

declare  @row3 as int, @data as date, @4order as varchar(50)

DECLARE myCursor CURSOR FOR
	 select ID_order
	 from (select ID_order , ROW_NUMBER() over(order by DATE desc) as row from order 
			where Id_customer=@id_customer) as tmp
			where  row=4
open myCursor;
FETCH NEXT FROM myCursor INTO @row4
WHILE @@FETCH_STATUS = 0 
	begin
	if (@row4=  @ID_order)
		begin
			set @4order= (select number from order where @ID_order=ID_order)
		end
	else 
			set @4order ='null' 
		
		FETCH NEXT FROM myCursor INTO @row4;
	end
	
CLOSE myCursor
DEALLOCATE myCursor

return @4order
end 
;

don't know if could be done

Thanks in advance

Posted 18-Nov-15 0:55am

Member 12146557

Updated 18-Nov-15 20:27pm

Tomas Takac

v2

Add a Solution

Comments

Corporal Agarn 18-Nov-15 9:13am

Why use the cursor?

Member 12146557 18-Nov-15 9:24am

I solved it in another way but I have it as a duty given to me to understand the cursor, means that I should solve it with cursor. thanks

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-11-25T05:35:00

Well I finally got your code to return the correct answer once I'd fixed all of the bugs...
ORDER and DATE are reserved words, so if you are going to use them as table or column names you must surround them with square brackets i.e. [order], [DATE]. Better still, choose different names. You also did not declare @row4.

In the code you have presented there is no need for a cursor at all because you have used ROW_NUMBER meaning you can target the 4th row directly - which is essentially what you have done with

where  row=4

NOTE: It is just as well that the query forming the cursor returns only a single row as you have the FETCH NEXT within an ELSE clause. If you are going to use a cursor then make sure you always fetch the next row otherwise you could end up in an infinite loop.

The following trivial example shows how to find the 4th row in a query using a cursor (CAVEAT - this is not a recommended approach and is used to demonstrate the cursor only)

I set up some sample data that contains an int with the numbers 1 to 19 inclusive and a varchar column that contains some text (the word 'text' followed by the same number as the id)

SQL

create table cursorExample
(
	id int identity(1,1),
	someData varchar(max)
)

declare @i int = 1
while @i < 20
begin
	insert into cursorExample values ('text' + cast(@i as varchar))
	set @i = @i + 1
end

Now imagine I want to find the 4th row from the end of that table. I.e. the 4th row of this query

SQL

select id, someData from cursorExample order by id DESC

I can do that with the following sql

SQL

declare @myInt int
declare @myVarchar varchar(max) 

declare @myDesiredInt int = 4
declare @myFoundVarchar varchar(max)
declare @myFoundInt int

declare @loopCount int = 1

declare myCursor cursor for
	select id, someData from cursorExample order by id DESC 

open myCursor
fetch next from myCursor into @myInt, @myVarChar
while @@FETCH_STATUS = 0
begin
	print cast(@loopCount as varchar) + ' : ' 
			+ @myVarChar + ' : ' + cast(@myInt as varchar) + ' : '
			+ isnull(cast(@myFoundInt as varchar), 'null') + ' : ' 
			+ isnull(@myFoundVarchar,'null')

	if @loopCount = @myDesiredInt
	begin
		set @myFoundVarchar = @myVarchar
		set @myFoundInt = @myInt
	end 
	set @loopCount = @loopCount + 1
	fetch next from myCursor into @myInt, @myVarChar
end
close myCursor
deallocate myCursor
print @myFoundVarchar
print @myFoundInt

Points to note:
- I'm using @loopcount to count the rows fetched from the cursor - I only set the "output" variables when I find the row I want.
- I increment @loopcount unconditionally within each loop
- I fetch the next row of the cursor unconditionally within the loop
- That long-winded print statement within the loop is just to demonstrate what is going on i.e.

1 : text19 : 19 : null : null
2 : text18 : 18 : null : null
3 : text17 : 17 : null : null
4 : text16 : 16 : null : null
5 : text15 : 15 : 16 : text16
6 : text14 : 14 : 16 : text16
7 : text13 : 13 : 16 : text16
8 : text12 : 12 : 16 : text16
9 : text11 : 11 : 16 : text16
10 : text10 : 10 : 16 : text16
11 : text9 : 9 : 16 : text16
12 : text8 : 8 : 16 : text16
13 : text7 : 7 : 16 : text16
14 : text6 : 6 : 16 : text16
15 : text5 : 5 : 16 : text16
16 : text4 : 4 : 16 : text16
17 : text3 : 3 : 16 : text16
18 : text2 : 2 : 16 : text16
19 : text1 : 1 : 16 : text16

Note - because the print statement is at the start of the loop then the desired results don't start appearing until the 5th loop.

Notice that the cursor runs "until the end" - which is one of the reasons that cursors can be a very inefficient way to extract data.

It is possible to "break out" of the cursor loop by introducing another variable

SQL

declare @abortLoop int = 0

, change the WHERE to be

SQL

while (@@FETCH_STATUS = 0 and @abortLoop = 0)

and set the new variable to another value once the desired values are found

set @myFoundVarchar = @myVarchar
set @myFoundInt = @myInt
set @abortLoop = 1

The output will then be

C#

1 : text19 : 19 : null : null
2 : text18 : 18 : null : null
3 : text17 : 17 : null : null
4 : text16 : 16 : null : null
text16
16