Using multiple cursors

[rk68]

Hi,
I have 2 tables. I has Store Code & date of last transactions uploaded.
The 2nd table has store code, date of transaction to be loaded & a tag whether its uploaded.
I have written a procedure where in I check the 1st table to get the store code & the last date of transction + 1 day.
If the above condition is met I update the 2nd table (tag field) with Y & have to continue for the next date of same store. If not available check the next store & the last updated date + one day from 1st table and repeat the process.
Here the procedure...pl help where I am going wrong as this updates only 1 record.
I m new to MS SQL.
==================
Create PROCEDURE [dbo].[DPIL_TRN_Upload2]
AS
Begin

declare @Loc_Cd1 VARCHAR(8) ;
declare @Loc_Cd2 VARCHAR(8) ;
declare @Odt1 datetime;
declare @Odt2 datetime;
declare @X_updt varchar(1) ;
declare @cursor1_status int;
declare @cursor2_status int;

DECLARE cur1 CURSOR FOR SELECT Location_code,ODT_Max +1 FROM [cartesian].[dbo].[test_c_summ] order by location_code, odt_max;

Delete from [Dpil].[dbo].[dpil_trn_summ];
Insert into [dpil].[dbo].[dpil_trn_summ] (location_code, order_date,t_upload) select distinct location_code, order_date, 'N' from [dpil].[dbo].[orders] where location_code in ('DDI71002','DPI65767') order by location_code, order_date;

DECLARE cur2 CURSOR FOR SELECT location_code,order_date,t_upload FROM [dpil].[dbo].[dpil_trn_summ] order by location_code, order_date;

OPEN cur1;

OPEN cur2;
fetch next from cur1 into @Loc_Cd1, @Odt1
select @cursor1_status = @@fetch_status
while @cursor1_status = 0

begin
fetch next from cur2 into @Loc_Cd2, @Odt2, @X_updt
select @cursor2_status = @@fetch_status


BEGIN
print 'before '+@Loc_Cd1+ ' date' + convert(varchar(10),@Odt1)
print 'before1 '+@Loc_Cd2 + ' date' + convert(Varchar(10),@Odt2);

while @cursor2_status = 0
BEGIN
if @Loc_Cd1 = @Loc_Cd2 and @Odt1 = @Odt2 AND @X_updt='N'
BEGIN
print 'l1 '+@Loc_Cd1;
print @Odt1;
print 'l2 '+@Loc_Cd2;
PRINT @ODT2;

update [dpil].[dbo].[dpil_trn_summ] set t_upload='Y' where location_code=@loc_cd1 and order_date=@Odt2 ;
update [cartesian].[dbo].[test_c_summ] set ODT_Max = @Odt2 where location_code=@loc_cd1 ;

fetch next from cur2 into @Loc_Cd2, @Odt2, @X_updt
select @cursor2_status = @@fetch_status
End;
Else
print 'xx1 '+@Loc_Cd1;
print @Odt1;

fetch next from cur2 into @Loc_Cd2, @Odt2, @X_updt
select @cursor2_status = @@fetch_status
END;

fetch next from cur2 into @Loc_Cd2, @Odt2, @X_updt
select @cursor2_status = @@fetch_status

END;

END;
fetch next from cur1 into @Loc_Cd1, @Odt1
select @cursor1_status = @@fetch_status


CLOSE cur2;


CLOSE cur1;

END

===========
Thanks
Raj

 

[simian336]

As cursors are generally avoided, perhaps you could provide some sample data and the expected results and we could help you with a solution.

Simi

 

[gmmastros]

It's hard to tell from your description of the process, but I suspect you don't need to use any cursors.

If you can show some sample data from both tables, and then show the expected results, I bet we could help you with a process to achieve the same goals without needing to use any cursor. This method (if it works) would execute faster and would likely involve a lot less code (meaning that it would be easier to maintain).

When posting sample data, only show a small handful of rows from each table, and do not include any sensitive data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rk68]

Table 1 (cursor 1)
Location Code Date of Upload
D1 17/05/2012
D2 21/05/2012

Table 2 (this summary table is created from transaction table creating summary location code & trn date) - cursor 2
Location Code Transaction Date Update Flag
D1 17/05/2012 N
D1 18/05/2012 N
D1 19/05/2012 N
D1 22/05/2012 N
D2 21/05/2012 N
D2 22/05/2012 N

Post running this procedure, output will be:
Location Code Date of Upload
D1 19/05/2012
D2 22/05/2012

Table 2 (this summary table is created from transaction table creating summary location code & trn date)
Location Code Transaction Date Update Flag
D1 17/05/2012 N
D1 18/05/2012 Y ... is updated
D1 19/05/2012 Y --- is updated
D1 22/05/2012 N
D2 21/05/2012 N
D2 22/05/2012 Y --- is updated

The procedure will check location & date wise from 1st table & check the location & date + 1 (i.e. next date) from table 2. If the same is available, it updates the table1 with that date & update the Update flag field as Y in table2. If the next date is not available (for e.g. D1 location has no transaction for 20 & 21), in such cases it has to check the next location.
Hope this is clear.

 

[gmmastros]

In Table 2, there is a row for D1 (22/05/2012). If I understand correctly, you don't want Table 1 updated with this data because there is a 2 day gap between the 19th and the 22nd. Do I understand this correctly?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rk68]

Yes. The table will for D1 will be update as 19/5.

 

[gmmastros]

Let's tackle this problem in pieces, shall we? It will be easier for me to explain, and probably easier for you to understand.

First, Let's look at the data from the Orders table. We want to get the location code and the maximum order data where there is no subsequent order date. If I am correct, the following query should return the data that you would like to have in "Table 1"

Select A.Location_Code, Max(B.Order_Date) As MaxTransactionDate
From   [dpil].[dbo].[orders] As A
       Inner Join [dpil].[dbo].[orders] As B
         On A.Location_Code = B.Location_Code
         And A.Order_Date = B.Order_Date - 1
Where  A.Location_Code In ('DDI71002','DPI65767')
Group By A.Location_Code

This type of query is called a "self join" where you basically join the same table back to itself. Notice the join condition. We are joining back to itself on location code and order date from one instance of the orders table = order date - 1 from the other instance of the table. This join, since it is an inner join, will only return rows where there are no gaps in the date. Next, we use a group by so that we can return a distinct list of location codes and the max order date.

I encourage you to run this query and analyze the results. This should return the data you ultimately want to have in "Table 1". If this does not return the correct data, please let me know and explain why it's not correct. If any part of this query does not make sense to you, please let me know and I will explain it more.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rk68]

Thanks George. I wont be in a position to run this query now as I cant access the server.
I would like to elaborate it further...
The Table 1 data is a summary pulled from my transaction data table showing the location code & max order date.
Table 2 data is summary of transaction dump received from client. Table 2 gives me summary of the location & unique dates for these location available in the dump. I am passing N as default value.
The procedure is to identify max date of a location from Table 1 & check the next date (i.e. max date +1) in Table 2. If available update the "Update Tag" field in Table 2 as Y and also update the Table 1 date against that location with max date + 1. Now it has to check the next date for that location. If available update as mentioned above else skip that location and go to next location. This process will happen for all location available in the dump v/s the table 1 location.
George, hope this is much more clear.

TIA
Raj

 

[gmmastros]

Have you had an opportunity to run the query yet? I'm curious to know if it returns the correct data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rk68]

Thanks. I have managed to run the procedure making changes in it.

 

[fredericofonseca]

and did you remove those cursors?
because that is what George was after showing you how to do.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[rk68]

I removed the 1st cursor. 2nd cursor remains as it is. The 1st cursor query is called after fetching the 2nd Cursor.