December 16, 2008 at 6:55 am
I have a piece of script (reconciliation) that uses 2 x CTE to create a CURSOR. Now I am sure I have read on the MSDN site that this is allowed but currently I am only looping 1 row when 2 are returned.
-- Y = Year, T = Transactions, A = Amount
DECLARE C1 CURSOR STATIC FORWARD_ONLY FOR
WITH stg_CTE (Y, T, A)
AS
(
SELECT ...
),
WITH dw_CTE (Y, T, A)
AS
(
SELECT ...
)
SELECT d.*, s.* FROM dw_CTE d LEFT JOIN stg_CTE s ON d.Y = s.Y
EXCEPT
SELECT s.*, d.* FROM stg_CTE s LEFT JOIN dw_CTE d ON s.Y = d.Y
The purpose of the above script is to return the mismatched rows inc. the values from both sources. Below are the results if used without a cursor:
Y, T, A, Y, T, A
2001, 1, 10.01, NULL, NULL, NULL
2007, 1, 9.21, 2007, 1, 10.00
Now if I use the cursor 'C1' it only reads the 1st row and not the 2nd, see code below;
IF (@@ROWCOUNT > 0)
BEGIN
OPEN C1
FETCH NEXT FROM C1 INTO @v_year, @v_count, @v_amount, @v_year_stg, @v_count_stg, @v_amount_stg
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Mismatch'
FETCH NEXT FROM C1 INTO @v_year, @v_count, @v_amount, @v_year_stg, @v_count_stg, @v_amount_stg
END
CLOSE C1
END
DEALLOCATE C1
I have printed out the '@@FETCH_STATUS' after the 2nd 'FETCH' statement and this reads -1 indicating no more rows but I have also printed out the '@@CURSOR_ROWS' value (after the OPEN statement) and it reads 2.
Does anyone know why my cursor is not working as expected.
P.S. I know I could re-factor this solution to eliminate the problem but would first like to see why this is working first.
Billy
December 16, 2008 at 10:53 am
I've found the answer. In my example; for clarity, I omitted the the code were I was Printing out all the Vars; and not just the text 'Mismatch', and as such this was the problem because if any of the Vars from the Cursor were NULL then the row would not be printed ... and as such; in my example, only one row would be printed (the one with no NULLS). I have now protected these Vars with ISNULL.
So to there is no problem using Cursors with CTE and EXCEPT.
December 16, 2008 at 11:17 am
My question is why are you using a cursor? Cursors do not scale well where a set-based solution would be better.
December 17, 2008 at 3:07 am
Thanks for your reply and I take on board about your first link. I am a little confused about your 2nd link as this seems to be talking about maintaining a running total ... which I am not doing. If it to highlight the fact that I could perform the same looping action without the need of a cursor then I understand this.
The task I have been given is to reconcile the data extracted from a Staging Database (holding 1 days data) with a Warehouse database (holding many years of data).
My solution is to query the staging tables and grouping them by years (resulting in approx. 2-3 rows) and then querying the Warehouse database FACT table for the new data and again grouping this by years. Then matching the Years with each other and then checking the record count and the 'sum' value for each year. So rather than performing a loop within a loop I use the EXCEPT keyword which works fine. The results of the EXCEPT indicate the mismatches and it these rows which I need to now loop to insert into a log table.
I could replace the cursor using a temp table or table var but I thought why bother as its only used if mismatches occur plus it will only be a couple of rows anyway.
What are your views?
Billy
December 17, 2008 at 6:27 am
b.b (12/17/2008)
Thanks for your reply and I take on board about your first link. I am a little confused about your 2nd link as this seems to be talking about maintaining a running total ... which I am not doing. If it to highlight the fact that I could perform the same looping action without the need of a cursor then I understand this.
Those links are very commonly linked articles that Lynn has put in his signature. He wasn't saying they applied in this case. (Although the top one applies in every case).
May want to put a line over those or something Lynn :hehe:.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply