from cursor to select, one step at a time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

from cursor to select, one step at a time

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-27 : 09:30:07

I am trying to rewrite a stored procedure that is using a cursor into a single select. I have changed one portion of the cursor and moved it into the initial select statement and it is working properly. I am stuck on the next portion though. The first query returns the proper number of records, however the second modification returns only a third of the records it should. I have tried both an inner and left outer join on the additional table, without success.

In the message pane I get this on the second revision, perhaps it has something to do with the problem:
Warning: Null value is eliminated by an aggregate or other SET operation.

First revision, proper number of records returned(11769):
----------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Policies_in_force_dev3]
@end_month SMALLINT,
@end_year SMALLINT
AS
SET rowcount 0

DELETE FROM policies_in_force_report_dev3

SET rowcount 0

DECLARE @base VARCHAR(12),
@suffix SMALLINT,
@max_entered_date DATETIME,
@endorse_type VARCHAR(2),
@underwriting_approved DATETIME,
@end_date DATETIME,
@in_force_date DATETIME

SELECT @end_date = Convert(CHAR(2),@end_month) + '/1/' + Convert(CHAR(4),@end_year)

SELECT @in_force_date = Dateadd(dd,-1,Dateadd(mm,1,@end_date))

DECLARE policy_cursor CURSOR READ_ONLY FOR
SELECT policy.policy_base,
policy.policy_suffix
FROM policy (NOLOCK)
INNER JOIN policy_information (NOLOCK)
ON policy.policy_base = policy_information.policy_base
AND policy.policy_suffix = policy_information.policy_suffix
WHERE current_flag = 'Y'
AND policy.expiration_date > @in_force_date
AND policy.inception_date < @in_force_date
AND policy_information.underwriting_approved <= @in_force_date

OPEN policy_cursor

FETCH NEXT FROM policy_cursor
INTO @base,
@suffix

WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @max_entered_date = Max(entered_date)
FROM endorsement (nolock)
WHERE entered_date < @in_force_date
AND policy_base = @base
AND policy_suffix = @suffix

IF NOT (@max_entered_date IS NULL)
BEGIN
SELECT @endorse_type = endorsement_type
FROM endorsement (nolock)
WHERE entered_date = @max_entered_date
AND policy_base = @base
AND policy_suffix = @suffix

IF @endorse_type <> 'CA'
BEGIN
INSERT INTO policies_in_force_report_dev3
(policy_base,
policy_suffix,
inforce_date)
VALUES (@base,
@suffix,
@in_force_date)
END
END
ELSE
BEGIN
INSERT INTO policies_in_force_report_dev3
(policy_base,
policy_suffix,
inforce_date)
VALUES (@base,
@suffix,
@in_force_date)
END

FETCH NEXT FROM policy_cursor
INTO @base,
@suffix
END

CLOSE policy_cursor

DEALLOCATE policy_cursor

----------------------------------------------------------------------
Second revision, not enough records returned(9220):
----------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Policies_in_force_dev4]
@end_month SMALLINT,
@end_year SMALLINT
AS
SET rowcount 0

DELETE FROM policies_in_force_report_dev4

SET rowcount 0

DECLARE @base VARCHAR(12),
@suffix SMALLINT,
@max_entered_date DATETIME,
@endorse_type VARCHAR(2),
@underwriting_approved DATETIME,
@end_date DATETIME,
@in_force_date DATETIME

SELECT @end_date = Convert(CHAR(2),@end_month) + '/1/' + Convert(CHAR(4),@end_year)

SELECT @in_force_date = Dateadd(dd,-1,Dateadd(mm,1,@end_date))

DECLARE policy_cursor CURSOR READ_ONLY FOR
SELECT policy.policy_base,
policy.policy_suffix,
Max(endorsement.entered_date)
FROM policy (NOLOCK)
INNER JOIN policy_information (NOLOCK)
ON policy.policy_base = policy_information.policy_base
AND policy.policy_suffix = policy_information.policy_suffix
LEFT JOIN endorsement (NOLOCK)
ON policy.policy_base = endorsement.policy_base
AND policy.policy_suffix = endorsement.policy_suffix
WHERE policy.current_flag = 'Y'
AND policy.expiration_date > @in_force_date
AND policy.inception_date < @in_force_date
AND policy_information.underwriting_approved <= @in_force_date
GROUP BY policy.policy_base,
policy.policy_suffix
HAVING ((Max(endorsement.entered_date) < @in_force_date)
OR (Max(endorsement.entered_date) IS NULL))

OPEN policy_cursor

FETCH NEXT FROM policy_cursor
INTO @base,
@suffix,
@max_entered_date

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF NOT (@max_entered_date IS NULL)
BEGIN
SELECT @endorse_type = endorsement_type
FROM endorsement (nolock)
WHERE entered_date = @max_entered_date
AND policy_base = @base
AND policy_suffix = @suffix

IF @endorse_type <> 'CA'
BEGIN
INSERT INTO policies_in_force_report_dev4
(policy_base,
policy_suffix,
inforce_date)
VALUES (@base,
@suffix,
@in_force_date)
END
END
ELSE
BEGIN
INSERT INTO policies_in_force_report_dev4
(policy_base,
policy_suffix,
inforce_date)
VALUES (@base,
@suffix,
@in_force_date)
END

FETCH NEXT FROM policy_cursor
INTO @base,
@suffix,
@max_entered_date
END

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-27 : 10:01:09

What do you think you want to do here?


IF @endorse_type <> 'CA'
BEGIN
INSERT INTO policies_in_force_report_dev3
(policy_base,
policy_suffix,
inforce_date)
VALUES (@base,
@suffix,
@in_force_date)
END
END
ELSE
BEGIN
INSERT INTO policies_in_force_report_dev3
(policy_base,
policy_suffix,
inforce_date)
VALUES (@base,
@suffix,
@in_force_date)
END

The code does the same thing regardless of the value of @endorse_type.

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-27 : 10:14:37

I think you could remove the whole cursor with something like this


INSERT INTO policies_in_force_report_dev3
(policy_base,
policy_suffix,
inforce_date)

with MaxEnteredDate (policy_base,policy_suffix, Max_Entered_date)
as
(SELECT policy_base,policy_suffix, Max(entered_date)  
	FROM endorsement (nolock)
	Group by policy_base, policy_suffix
	WHERE entered_date < @in_force_date
)
SELECT policy.policy_base,policy.policy_suffix,M.Max_Entered_Date
FROM policy (NOLOCK)
INNER JOIN policy_information  PI ON policy.policy_base = pi.policy_base
	AND policy.policy_suffix = pi.policy_suffix
Join MaxEnteredDate M on M.Policy_Base = Policy.Policy_base 
	and M.Policy_suffix = policy.policy_suffix
WHERE current_flag = 'Y'
	AND policy.expiration_date > @in_force_date
	AND policy.inception_date < @in_force_date
	AND policy_information.underwriting_approved <= @in_force_date

I wasn't able to check for errors since you didn't provide schema, sample data, etc.. and I didn't check for syntax problems but hopefully this will get you moving in the right direction.

An infinite universe is the ultimate cartesian product.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-27 : 16:12:52

Thanks for the help. I had to play with the syntax a bit to get it to compile. I'm trying to emulate the original logic but now I'm only getting 3635 returned when it should be 11769. Any suggestions?

WITH maxentereddate(policy_base,policy_suffix,max_entered_date)
AS (SELECT policy_base,
policy_suffix,
Max(entered_date)
FROM endorsement WITH (nolock)
WHERE entered_date < @in_force_date
GROUP BY policy_base,
policy_suffix)
INSERT INTO policies_in_force_report_dev5
(policy_base,
policy_suffix,
inforce_date)
SELECT policy.policy_base,
policy.policy_suffix,
m.max_entered_date
FROM policy WITH (NOLOCK)
INNER JOIN policy_information
ON policy.policy_base = policy_information.policy_base
AND policy.policy_suffix = policy_information.policy_suffix
INNER JOIN endorsement
ON policy.policy_base = endorsement.policy_base
AND policy.policy_suffix = endorsement.policy_suffix
JOIN maxentereddate m
ON m.policy_base = policy.policy_base
AND m.policy_suffix = policy.policy_suffix
WHERE policy.current_flag = 'Y'
AND policy.expiration_date > @in_force_date
AND policy.inception_date < @in_force_date
AND policy_information.underwriting_approved <= @in_force_date
AND ((m.max_entered_date IS NOT NULL
AND endorsement.entered_date = m.max_entered_date
AND endorsement.endorsement_type <> 'CA')
OR (m.max_entered_date IS NULL))

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-27 : 16:43:32

How do you know it should be 11769?

An infinite universe is the ultimate cartesian product.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-27 : 16:49:42

That is what is returned by the original stored procedure I am trying to convert from a cursor to a select. It is the number of records returned from the first stored procedure in my first post. Same data, same tables, different result sets.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-27 : 19:37:49

The first SP has a logic error that I pointed out before. Were you ever able to figure out why that superfluous IF statement was there?

So on to record counts. You have an inner join on endorsement in your latest modified code. Why? The only place I see information from endorsement used in the original code is to find a corresponding max date.

What happens if you run this?


WITH maxentereddate(policy_base,policy_suffix,max_entered_date)
AS (
	SELECT policy_base,
			policy_suffix,
			Max(entered_date)
	FROM endorsement WITH (nolock)
	GROUP BY policy_base,policy_suffix
   )
SELECT COUNT(*)
FROM policy WITH (NOLOCK)
JOIN policy_information 
	ON    policy.policy_base = policy_information.policy_base
	AND policy.policy_suffix = policy_information.policy_suffix
left JOIN maxentereddate m
	ON    m.policy_base = policy.policy_base
	AND m.policy_suffix = policy.policy_suffix
WHERE policy.current_flag = 'Y'

An infinite universe is the ultimate cartesian product.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-28 : 09:28:03

I never figured out why there was the superfluous if statement.

The query you listed returns 1587037.

I added the inner join to get this into the where clause of the query:
AND ((m.max_entered_date IS NOT NULL
AND endorsement.entered_date = m.max_entered_date
AND endorsement.endorsement_type <> 'CA')
OR (m.max_entered_date IS NULL))

It was my attempt to get the same result as this portion of the original code:

BEGIN
SELECT @endorse_type = endorsement_type
FROM endorsement (nolock)
WHERE entered_date = @max_entered_date
AND policy_base = @base
AND policy_suffix = @suffix

IF @endorse_type <> 'CA'
BEGIN
INSERT INTO policies_in_force_report
(policy_base,
policy_suffix,
inforce_date,
policy_state,
program)
VALUES (@base,
@suffix,
@in_force_date,
@state,
@program)
END
END
ELSE
BEGIN
INSERT INTO policies_in_force_report
(policy_base,
policy_suffix,
inforce_date,
policy_state,
program)
VALUES (@base,
@suffix,
@in_force_date,
@state,
@program)
END

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-28 : 10:00:00

how many rows do you get with this?


WITH maxentereddate(policy_base,policy_suffix,max_entered_date)
AS (
	SELECT policy_base,
			policy_suffix,
			Max(entered_date)
	FROM endorsement WITH (nolock)
	GROUP BY policy_base,policy_suffix
   )
SELECT  distinct policy.policy_base,policy.policy_suffix,m.max_entered_date
FROM policy WITH (NOLOCK)
JOIN policy_information 
	ON    policy.policy_base = policy_information.policy_base
	AND policy.policy_suffix = policy_information.policy_suffix
left JOIN maxentereddate m
	ON    m.policy_base = policy.policy_base
	AND m.policy_suffix = policy.policy_suffix
WHERE policy.current_flag = 'Y'

And then with this?


WITH maxentereddate(policy_base,policy_suffix,max_entered_date)
AS (
	SELECT policy_base,
			policy_suffix,
			Max(entered_date)
	FROM endorsement WITH (nolock)
	GROUP BY policy_base,policy_suffix
   )
SELECT  distinct policy.policy_base,policy.policy_suffix,m.max_entered_date
FROM policy WITH (NOLOCK)
JOIN policy_information 
	ON    policy.policy_base = policy_information.policy_base
	AND policy.policy_suffix = policy_information.policy_suffix
JOIN maxentereddate m
	ON    m.policy_base = policy.policy_base
	AND m.policy_suffix = policy.policy_suffix
WHERE policy.current_flag = 'Y'

The reason I asked about the superfluous code is that you included this

AND endorsement.endorsement_type <> 'CA'

back into your code when clearly it doesn't matter what the endorsement type is. The record will be inserted regardless of endorsement_type's value in the original code. So absent a requirement behind that code that isn't being met, it should be removed since it only serves to confuse.

An infinite universe is the ultimate cartesian product.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-28 : 10:40:35

first query:
1587037

second query:
1142086

Is the portion of code really superfluous? I am reading it like this:
if the max entered date is not null AND endorse_type <> 'CA'
insert
if the max entered date is null
insert

What about the records in which the max entered date is not null and the endorse_type = 'CA'. those records are excluded, aren't they?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-28 : 12:22:11

OK I see. You're correct that series of ugly nested IF statements is filtering out 'CA' (It's like staring into the sun...)

So....

How about this.


WITH    maxentereddate ( policy_base, policy_suffix, max_entered_date )
          AS ( SELECT   policy_base,
                        policy_suffix,
                        Max(entered_date)
               FROM     endorsement WITH ( nolock )
               WHERE    entered_date < @in_force_date
                        AND endorsement_type <> 'CA'
               GROUP BY policy_base,
                        policy_suffix
             )
            SELECT  Distinct
                    policy.policy_base,
                    policy.policy_suffix,
                    m.max_entered_date
            FROM    policy WITH ( NOLOCK )
                    JOIN policy_information ON policy.policy_base = policy_information.policy_base
                                                     AND policy.policy_suffix = policy_information.policy_suffix
                    JOIN maxentereddate m ON m.policy_base = policy.policy_base
                                             AND m.policy_suffix = policy.policy_suffix
            WHERE   policy.current_flag = 'Y'
                    AND policy.expiration_date > @in_force_date
                    AND policy.inception_date < @in_force_date
                    AND policy_information.underwriting_approved <= @in_force_date

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-28 : 12:33:35

Here's another one to consider


WITH    maxentereddate ( policy_base, policy_suffix, max_entered_date )
          AS ( SELECT   policy_base,
                        policy_suffix,
                        Max(entered_date)
               FROM     endorsement WITH ( nolock )
               WHERE    entered_date < @in_force_date
               GROUP BY policy_base,
                        policy_suffix
             )
            SELECT  policy.policy_base,
                    policy.policy_suffix,
                    m.max_entered_date
            FROM    policy WITH ( NOLOCK )
                    JOIN policy_information ON policy.policy_base = policy_information.policy_base
                                                     AND policy.policy_suffix = policy_information.policy_suffix
                    JOIN maxentereddate m ON m.policy_base = policy.policy_base
                                             AND m.policy_suffix = policy.policy_suffix
                    JOIN endorsement ON m.policy_base = endorsement.policy_base
                                    AND m.policy_suffix = endorsement.policy_suffix
                                    AND m.max_entered_date = endorsement.entered_date
            WHERE   policy.current_flag = 'Y'
                    AND endorsement.endorsement_type <> 'CA'
                    AND policy.expiration_date > @in_force_date
                    AND policy.inception_date < @in_force_date
                    AND policy_information.underwriting_approved <= @in_force_date

This will exclude records if the corresponding endorsement record with the most recent entered date is from CA.

see the difference?

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-01 : 17:27:10

So did you ever get this working?

An infinite universe is the ultimate cartesian product.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-09-02 : 14:05:23

quote:
Originally posted by cat_jesus

So did you ever get this working?

No, never got it working. Can't figure out where it is off. The cursor only takes about a minute to run during a 20 minute report generation, so it is not the end of the world. I wanted a single query for both speed but also because it is more "correct".

I do appreciate all the help though. Thank you.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-02 : 14:46:54

How do you validate the results of the original? Or is the accuracy not questioned?

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources