Comparing 2 cursors in a stored procedure

[muhaidib]

Hi All,
I have created 2 cursors in a 2 stored procedures. I want to search records of 2nd cursor with each record of 1st cursor. Somehow I am stuck with structuring the procedure. Can someone help me with it?

My current procedure looks as follows:

DECLARE @CrsrBankVar CURSOR
DECLARE @CrsrCashVar CURSOR

/* Execute the procedure created earlier to fill the variable. */
EXEC OpenBKCrsr @BKNoMatch = @CrsrBankVar OUTPUT
EXEC OpenCBCrsr @CBNoMatch = @CrsrCashVar OUTPUT


/* Declaring variables to store data from cursor @CrsrBankVar */
DECLARE @BkDate as decimal(9,0),
@BkAmt as decimal(9,3)

/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrBankVar INTO @BkDate, @BkAmt
WHILE (@@FETCH_STATUS = 0)
BEGIN

**************************
Here I want to compare each row from @CrsrCashVar with the
variables defined above
**** Need help to do so ***********
**************************
print @BkDate
print @BkAmt
PRINT '****************'

FETCH NEXT FROM @CrsrBankVar
END

CLOSE @CrsrBankVar
CLOSE @CrsrCashVar

DEALLOCATE @CrsrBankVar
DEALLOCATE @CrsrCashVar
GO

 

[hmckillop]

This strikes me as a very odd way of doing things, particuarly using cursors.
It doesnt even look like a format I know, but cursors should be avoided where possible.

What code is within your stored procedure OpenBKCrsr and OpenCBCrsr .
I would guess that you could compare fairly much 95% of scenarios without need of a cursor.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[muhaidib]

Cursors OpenBkCrsr and OpenCBCrsr contains as follows:
OpenBkCrsr:
Select BkRef, BkDT, BkAmt from BK where bkdt >= '20090101' and bkdt <= '20090131'
OpenCBCrsr:
Select CbRef, CbDT, CbAmt from CB where cbDt >= '20090101' and cbDt <= '20090131'

These are like 2 recordsets, ref fields has no relevance. Hence I want to extract matching records based on date and amount.

You may kindly give me structure to do so without using cursors.

Thanks

 

[SQLSister]

Select CbRef,BkRef, BkDT, BkAmt  from
(Select BkRef, BkDT, BkAmt from BK where bkdt >= '20090101' and bkdt <= '20090131')a
join
(Select CbRef, CbDT, CbAmt from CB where cbDt >= '20090101' and cbDt <= '20090131')b
where a.BkDT = b.CbDT and a.BkAmt=b.CbAmt

"NOTHING is more important in a database than integrity." ESquared

 

[muhaidib]

Thanks for reply.

Somehow I am getting error at "where"
Individual SELECT statements lists desired records. but Join statemet generates error.

 

[markros]

Try - slightly modified version - I think this is what SQLSister meant:

Select CbRef,BkRef, BkDT, BkAmt  from(Select BkRef, BkDT, BkAmt from BK where bkdt >= '20090101' and bkdt <= '20090131') a 
join(Select CbRef, CbDT, CbAmt from CB where cbDt >= '20090101' and cbDt <= '20090131') b ON a.BkDT = b.CbDT and a.BkAmt=b.CbAmt

 

[muhaidib]

Thanks a lot. It did not give error. I need to run the query on following data
Table BK
BkRef,BkDT,BkAmt
ZAFD-00257,20090101,-500.000
ZAFD-00258,20090101,-500.000
ZAFD-00259,20090101,-500.000
ZAFD-00260,20090101,-500.000
ZAFD-00261,20090101,-500.000
ZAFD-00262,20090101,-500.000

Table CB
CbRef,CbDT,CbAmt
912101-001,20090101,-500.000
912101-001,20090101,-500.000
912101-001,20090101,-500.000
912101-001,20090101,-500.000
912101-001,20090101,-500.000
912101-001,20090101,-500.000

Desired result is
BkRef,CbRef,CbDT,CbAmt
ZAFD-00257,912101-001,20090101,-500.000
ZAFD-00258,912101-001,20090101,-500.000
ZAFD-00259,912101-001,20090101,-500.000
ZAFD-00260,912101-001,20090101,-500.000
ZAFD-00261,912101-001,20090101,-500.000
ZAFD-00262,912101-001,20090101,-500.000

Need guidance

 

[markros]

In your CB table all records seem to be the same. How exactly do you want to join your tables together? Take all records from the BK and join with one unique record from CB?

It is not clear from your data what exactly do you want and what CB table represents.

 

[muhaidib]

Reference fields in the tables has no relevance to each other. Both the tables are sorted on Date and Amount.
I want to start from table Bk.
Take a record and match with records with CB.
If match is found Mark/ Extract both the records
else
pickup second record from table BK and repeat the process.

Hope this gives you idea of what I want to achieve.

 

[markros]

What you're describing sounds like an INNER JOIN and doesn't require cursors. However, it is still not clear for me what represents a match and what should you do if more than one record is matching.

 

[muhaidib]

As you come across first match with reference to Date and Amount, the matched records should be dropped from further matching. Hence when the first record from BK
ZAFD-00257,20090101,-500.000 and first records drom CB 912101-001,20090101,-500.000 should not be considered in next iteration.

Hope this is clear.

 

[markros]

I see now, thanks. Too complex now for me to express in one select operation and unfortunately I can not spend time right now on the set solution.

 

[starsky51]

Hi muhaidib,
Seeing as markros is tied up I thought i'd have a stab at it. The following code makes a temporary copy of the CB and adds an 'avail' to each record. As each BK record is matched up with a CB record and added to the output table, the avail field is changed from 1 to 0 to make sure that that CB row isn't considered in future.

DECLARE @CbTmp TABLE (CbRef char(10), CbDt smalldatetime, CbAmt int, avail bit);
DECLARE @out TABLE (BkRef char(10), CbRef char(10) NULL, CbDt smalldatetime NULL, CbAmt int NULL);
DECLARE @BkRef char(10), @BkDt smalldatetime, @BkAmt int;
DECLARE bkcur CURSOR FOR SELECT * FROM BK;
INSERT INTO @CbTmp SELECT CB.*, 1 FROM CB;
OPEN bkcur;
FETCH NEXT FROM bkcur INTO @BkRef, @BkDt, @BkAmt;
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO @out
	SELECT TOP(1) BkRef, CbRef, CbDt, CbAmt FROM Bk b LEFT JOIN @CbTmp t ON (b.BkDt = t.CbDt AND b.BkAmt = t.CbAmt AND t.Avail = 1) WHERE BkRef = @BkRef;
	UPDATE TOP(1) @CbTmp SET avail = 0 WHERE CbDt = @BkDt AND CbAmt = @BkAmt AND avail = 1;
	FETCH NEXT FROM bkcur INTO @BkRef, @BkDt, @BkAmt;
END
SELECT * FROM @CbTmp;
SELECT * FROM @out;
CLOSE bkcur;

 

[fredericofonseca]

I may be wrong on what you really want, but the following based on your input data gives the disired result.

Select distinct
       BkRef,
       CbRef,
       BkDT,
       BkAmt
 from
      (Select BkRef,
              BkDT,
              BkAmt
         from BK
        where bkdt >= '20090101'
          and bkdt <= '20090131') a
 INNER JOIN
      (Select CbRef,
              CbDT,
              CbAmt
              from CB
        where cbDt >= '20090101'
          and cbDt <= '20090131') b
  ON a.BkDT = b.CbDT
 and a.BkAmt=b.CbAmt

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886