By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,206 Members | 1,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,206 IT Pros & Developers. It's quick & easy.

Cursor Based SQL?

P: n/a
Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.
Jul 20 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Philip Mette wrote:
Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.

Don't listen to Java/C#/VB/<insert proc language of your choice> developers.
cursors and SQL are mutually exclusive.
A cursor is a way to interface SQL to procedural languages.

Cheers
Serge
Jul 20 '05 #2

P: n/a

"Philip Mette" <ph******@msn.com> wrote in message
news:96*************************@posting.google.co m...
Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.


You should never use a cursor unless there is no other possible solution -
TSQL is a set-based language, and MSSQL is heavily optimized to run
set-based code. Cursors are extremely slow, and they are not often
required - if you can give more details of what you're trying to do, someone
may be able to suggest a set-based approach.

If you do post more information, please include CREATE TABLE statements,
INSERTs for sample data, and sample output to make it easier to understand
what you're doing.

Simon
Jul 20 '05 #3

P: n/a
On 25 Aug 2004 07:26:50 -0700, Philip Mette wrote:
Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.


Hi Philip,

Why do you think that you have to create SQL that can loop through rows
(not records!!)? About 95% of the times that someone thinks that looping
is the only way to solve a problem, it isn't. And a set-based solution is
faster than a cursor in 99.9% of these situations.

Why don't you post some more info about the problem you're trying to solve
instead of focussing on a (probably sub-optimal) choice of how to solve
it. Chances are that someone will help you find a set-based solution to
your problem.

The best way to post a problem is by supplying the table structure (as
copy-and-pasteable CREATE TABLE statements, including constraints), sample
data (as copy-and-pastteable INSERT statements) and expected output along
with a description of the business problem you're trying to solve and (if
you have it) your current SQL. http://www.aspfaq.com/etiquette.asp?id=5006
and http://vyaskn.tripod.com/code.htm#inserts provide some help on putting
together the information for a good posting.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #4

P: n/a
Philip Mette (ph******@msn.com) writes:
Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.


Here is a quick example which says everything you need to know:

create procedure TSQL_ProductProc (@product int output)
as
begin
declare @sales int
declare c INSENSITIVE cursor for select intcolumn from tbl
open c

set @product = NULL

while 1 = 1
begin
fetch next from c into @sales
IF @@fetch_status <> 0
BREAK
set @product = coalesce(@product, 1) * @sales
end

close c
deallocate c
end

Notes:

1) Always make cursors INENSITIVE. The default keyset cursor is
incomprehensible, to me at least, and has given me more than one bad
negative performance surprise. (Might have happened more in SQL 6.5.)

2) By putting the FETCH statement first in the loop, the code does not
only get shorter, it is also easier to maintain. If you have two
FETCH statements, and one is at the end of a long loop, you can
easily forget to augment that statement if you add another column
to the cursor, giving difficult-to-find errors, since the cursors
works on the first iteration in the loop.

Incidently, this example is a case a cursor might be necessary, as
there is no way to do this sort of thing in a set-based statment.
(You can use SUM with LOG and EXP for a product, if you only have
positive values, but not in the general case.)

As others have pointed out, cursors should only be used as a last
resort. First try to find a set-based way of attacking the problem.
For a large data set, the difference in performance may several
magnitudes.

But using cursors can sometimes cut development time considerably.
Say that you have a stored procedure that performs the task you
want to do on a set of scalar values received as parameters. Writing
a cursor loop which calls that procedure is a simple task. Rewriting
the stored procedure to handle an input set, may be a major undertaking.
And if you know that you the data set you will loop over will be modest
in size, it may simply not be worth the cost.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

P: n/a
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Philip Mette (ph******@msn.com) writes:
Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.


Here is a quick example which says everything you need to know:

create procedure TSQL_ProductProc (@product int output)
as
begin
declare @sales int
declare c INSENSITIVE cursor for select intcolumn from tbl
open c

set @product = NULL

while 1 = 1
begin
fetch next from c into @sales
IF @@fetch_status <> 0
BREAK
set @product = coalesce(@product, 1) * @sales
end

close c
deallocate c
end

Notes:

1) Always make cursors INENSITIVE. The default keyset cursor is
incomprehensible, to me at least, and has given me more than one bad
negative performance surprise. (Might have happened more in SQL 6.5.)

2) By putting the FETCH statement first in the loop, the code does not
only get shorter, it is also easier to maintain. If you have two
FETCH statements, and one is at the end of a long loop, you can
easily forget to augment that statement if you add another column
to the cursor, giving difficult-to-find errors, since the cursors
works on the first iteration in the loop.

Incidently, this example is a case a cursor might be necessary, as
there is no way to do this sort of thing in a set-based statment.
(You can use SUM with LOG and EXP for a product, if you only have
positive values, but not in the general case.)

As others have pointed out, cursors should only be used as a last
resort. First try to find a set-based way of attacking the problem.
For a large data set, the difference in performance may several
magnitudes.

But using cursors can sometimes cut development time considerably.
Say that you have a stored procedure that performs the task you
want to do on a set of scalar values received as parameters. Writing
a cursor loop which calls that procedure is a simple task. Rewriting
the stored procedure to handle an input set, may be a major undertaking.
And if you know that you the data set you will loop over will be modest
in size, it may simply not be worth the cost.


---Ok I will send you the table and fields names along with the psuedo
logic of what i need done to get you started since you ask.:) I will
send the create table and data statements soon. Here is what I need.

A collector takes or makes calls. During this call he gets a PTP or
promise to pay. This activity is stored in tblDailyActivities and the
activity status is 'OP' or Open by default. In another table,
tblDailyPayments store payments that have come in through out the
month.
Here are the tables:
tblDailyActivities
Record_Key_Num
Acct_Num
Activity_Date (Date Promise to Pay Was Made)
Promise_Amt_1
Due_Date (Date Next Payment Is Due)
Promise_Status
Total_Dollars_Coll

tblDailyPayments
Record_Key_Num
Acct_Num
Pay_Amt
Activity_Date (Date Payment Was Made)
Here is the logic.(Keep in mind that their can be more than one
promise made on same acct by the same or different collector so the
logic is handling this, you will see.)

For Each [Acct_Num and Due_Date] combination
Look for payments from the tblDailyPayments Table with an
activity_date that are within tblDailyActivites.Activity_Date + 10
days.
If the sum of Payments>=90% of
tblDailyActivities.Promise_Amt_1 then
Update tblDailyActivities.Promise_Status =
'PK' and tblDailyActivites.Total_Dollars_Coll= 'Sum (of Payments)'
And all other same [Acct_Num and Due_Date]
combination's update tblDailyActivities.Promise_Status = 'IP'
Else If the sum of Payments<90% of
tblDailyActivities.Promise_Amt_1, then
If tblDailyActivites.Activity_Date +10 days > today's date, then
tblDailyActivities.Promise_Status= 'OP' and exit loop
Else If tblCacsDailyActivites.Activity_Date +10 days < today's
date, then
Update tblCacsDailyActivities.Promise_Status = 'PB'
And move to record 2 of same [Acct_Num and Due_Date] combination.

And repeat until end of loop.

HERES SOME DATA SAMPLE:

TBLDAILYACTIVITIES
RecKey Account ActivityDate DueDate UserId PromiseAMt PromiseDate
PromiseStatus TotalColl
688849 7339344 08/06/2004 08/30/2004 MPJ5 525.00 08/16/2004
OP <Null>
688850 7339344 08/07/2004 08/30/2004 MPJ5 525.00 09/15/2004
OP <Null>

688851 7339344 08/20/2004 09/30/2004 MPLI 600.00 09/15/2004
OP <Null>
688852 7339344 08/21/2004 09/30/2004 MPLT 700.00 10/15/2004
OP <Null>

688853 6116655 08/12/2004 08/30/2004 MP60 500.00 09/15/2004
OP <Null>
688854 6116655 08/12/2004 08/30/2004 MP61 550.00 09/15/2004
OP <Null>

TBLDAILY PAYMENTS
RecKey Account PayAmt ActivityDate
1254 7339344 225.00 08/10/2004
1255 7339344 295.00 08/11/2004
1259 7339344 300.00 08/20/2004
1354 6116655 250.00 08/13/2004

OUTPUT
RecKey Account ActivityDate DueDate UserId PromiseAMt PromiseDate
PromiseStatus TotalColl
688849 7339344 08/06/2004 08/30/2004 MPJ5 525.00 08/16/2004
PK 520.00
688850 7339344 08/06/2004 08/30/2004 MPJ5 525.00 09/15/2004
IP <Null>

688851 7339344 08/20/2004 09/30/2004 MPLI 525.00 09/15/2004
OP 300.00
688852 7339344 08/21/2004 09/30/2004 MPLT 525.00 10/15/2004
IP <Null>

688853 6116655 08/12/2004 08/30/2004 MP60 500.00 09/15/2004
PB 250.00
688854 6116655 08/12/2004 08/30/2004 MP61 550.00 09/15/2004
IP <Null>
THE FIRST TABLE REPRESENTS ACTIVITIES THE SECOUND PAYMENTS THAT COME
IN.
THE 3RD IS HOW THE DATA SHOULD LOOK. THIS IS BASED OFF OF ACCT AND
DUEDATE COMBINATIONS SO THE LOOP
WILL STARTS OVER ON THE SAME ACCT IF THE DUEDATE CHANGES. SINCE THE
2ND GROUP OF RECORDS STILL
IS IN THE 10 DAY WINDOW FROM ACTIVITY DATE TO TODAY WE KEEP IT OPEN
EVEN THOUGH THE AMOUNT IS LESS THAN 90%.
BECAUSE THEY HAVE A FEW MORE DAYS TO GET MORE PAYMENTS IN TO MAKE THIS
A PK. THE GOAL IS TO GET THE FIRST RECORD OF A
ACCT DUEDATE GROUP MARK IT EITHER PK OR PB AND MOVE TO THE NEXT UNTILL
YOU FIND A PK ONCE A PK IS FOUND MARK ALL OTHERS WITH
IN THAT GROUP IP. TOTAL DOLLARS IS ONLY RECORDED IN PK OP AND PB.









If this can be done without cursors I will be very thankful. You all
out on the posting boards have been great. Thanks so much for all your
assistance. You make SQL coding exciting.
Jul 20 '05 #6

P: n/a
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Philip Mette (ph******@msn.com) writes:
Does anyone have any good references they could recommend on Cursor
based SQL writing? I have to create SQL that can loop though records
simular to VB loops and I have been told that this is the way to go.
Any recommendations would be helpful.


Here is a quick example which says everything you need to know:

create procedure TSQL_ProductProc (@product int output)
as
begin
declare @sales int
declare c INSENSITIVE cursor for select intcolumn from tbl
open c

set @product = NULL

while 1 = 1
begin
fetch next from c into @sales
IF @@fetch_status <> 0
BREAK
set @product = coalesce(@product, 1) * @sales
end

close c
deallocate c
end

Notes:

1) Always make cursors INENSITIVE. The default keyset cursor is
incomprehensible, to me at least, and has given me more than one bad
negative performance surprise. (Might have happened more in SQL 6.5.)

2) By putting the FETCH statement first in the loop, the code does not
only get shorter, it is also easier to maintain. If you have two
FETCH statements, and one is at the end of a long loop, you can
easily forget to augment that statement if you add another column
to the cursor, giving difficult-to-find errors, since the cursors
works on the first iteration in the loop.

Incidently, this example is a case a cursor might be necessary, as
there is no way to do this sort of thing in a set-based statment.
(You can use SUM with LOG and EXP for a product, if you only have
positive values, but not in the general case.)

As others have pointed out, cursors should only be used as a last
resort. First try to find a set-based way of attacking the problem.
For a large data set, the difference in performance may several
magnitudes.

But using cursors can sometimes cut development time considerably.
Say that you have a stored procedure that performs the task you
want to do on a set of scalar values received as parameters. Writing
a cursor loop which calls that procedure is a simple task. Rewriting
the stored procedure to handle an input set, may be a major undertaking.
And if you know that you the data set you will loop over will be modest
in size, it may simply not be worth the cost.


Ok I realize my last post was probably difficult to follow so mabye
someone can interpret this. I know how to write what I want in Oracle
SQL can someone convert it for me . I do not know MS SQL enough to
make the change.
Here is the code:

CREATE OR REPLACE PROCEDURE UPD_ACTIVITY IS
CURSOR ACT_cur1
IS
SELECT DISTINCT A.ACCT_NUM, A.ACTIVITY_DATE
FROM TBLCACSDAILYACTIVITIES A ; /* this picks the unique
combination of acct and date*/

tot_pay number ;
rec_upd varchar2(1);
todays_date date;
BEGIN
dbms_output.put_line('inside upd_activity ');
SELECT SYSDATE INTO todays_date FROM DUAL;
FOR ACT_CUR_REC1 IN ACT_CUR1
LOOP
rec_upd := 'N';
DECLARE
CURSOR ACT_CUR2 IS
SELECT B.ACCT_NUM,
B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,
B.TOTAL_DOLLARS_COLL
FROM TBLCACSDAILYACTIVITIES B
WHERE B.ACCT_NUM = ACT_CUR_REC1.ACCT_NUM
AND B.ACTIVITY_DATE = ACT_CUR_REC1.ACTIVITY_DATE
ORDER BY B.ACTIVITY_DATE
FOR UPDATE OF B.PROMISE_STATUS,B.TOTAL_DOLLARS_COLL
NOWAIT;
BEGIN
dbms_output.put_line('inside upd_activity1111 ');
FOR ACT_CUR_REC2 IN ACT_CUR2
LOOP
BEGIN
SELECT SUM(C.PAY_AMT) into tot_pay
FROM TBLCACSDAILYPAYMENTS C
WHERE C.ACCT_NUM = ACT_CUR_REC2.ACCT_NUM
AND C.ACTIVITY_DATE >= ACT_CUR_REC2.ACTIVITY_DATE
AND C.ACTIVITY_DATE < ACT_CUR_REC2.ACTIVITY_DATE+10;
dbms_output.put_line('tot_pay =');
IF tot_pay >= .9 * ACT_CUR_REC2.PROMISE_AMT_1 THEN
IF rec_upd = 'N' THEN /* recs have not bee updated for promise
status*/
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'PK',
TOTAL_DOLLARS_COLL = tot_pay
WHERE CURRENT OF ACT_CUR2;
rec_upd := 'Y';
ELSIF rec_upd = 'Y' THEN
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'IP'
WHERE CURRENT OF ACT_CUR2;
END IF;
ELSIF tot_pay < .9 * ACT_CUR_REC2.PROMISE_AMT_1 THEN
IF (ACT_CUR_REC2.ACTIVITY_DATE+10) > todays_date THEN
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'OP'
WHERE CURRENT OF ACT_CUR2;
ELSE
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'PB'
WHERE CURRENT OF ACT_CUR2;
END IF;
END IF;
END;
END LOOP; /* end of ACT_CUR_REC2 loop */
END;
END LOOP; /*end of ACT_CUR_REC1 */
END ; /* end of proc */
/
Thanks again!
Jul 20 '05 #7

P: n/a
Philip Mette (ph******@msn.com) writes:
---Ok I will send you the table and fields names along with the psuedo
logic of what i need done to get you started since you ask.:) I will
send the create table and data statements soon. Here is what I need.


Yes, this appears to be doable with set-based statements, an outline
follows. Had you included your tables and data with CREATE TABLE and
INSERT statements, I would have been able to test my solution.

I also need to insert the disclaimer that I don't think I understand
the business requirements completely. The algorithm indeeds looks funny
to me. What if for the first two rows there are payments for the first
promise? What if there are payments that match more than one combination?

And there is a contradiction between verbal description and the Oracle
code you posted. The narrative says account/duedate, but the Oracle
code does not touch duedate. ActivityDate appears to better macthc results.

-- First we need some extra columns to play with, so we move to a temp
-- table. Here create with SELECT INTO to save some typing.
SELECT *, isfirst = convert(bit, 0) INTO #t FROM TBLCACSDAILYACTIVITIES

-- Set is first bit.
UPDATE #t
SET isfirst = 1
FROM #t
JOIN (SELECT RecKey = MIN(RecKey)
FROM #t
GROUP BY Account, ActivityDate) AS c
ON #t.RecKey = c.RecKey

-- Set totals.
UPDATE #t
SET TotalColl = p.payamt
FROM #t t
JOIN (SELECT t.Account, t.ActivityDate, payamt = SUM(t.Payment)
FROM #t
JOIN dailypayments d
ON t.account = p.account
AND p.ActivityDate BETWEEN t.ActivityDate AND t.ActivityDate + 10
GROUP BY t.account, t.ActivtithDate) AS s
ON t.Account = s.Account
AND t.AcctivityDate = s.ActivityDate

-- Now clear total where we should save, and set status
UPDATE #t
SET TotalCol1 = CASE WHEN TotalCol1 > 0.9 * PromsieAmt AND isfirst = 1
THEN TotalCol1
ELSE NULL
END,
Status = CASE WHEN TotalCol1 > 0.9 * PromsieAmt
THEN CASE isfirst WHEN 1 THEN 'PK' ELSE 'IP' END
ELSE CASE WHEN dateadd(DAY, ActivityDate, 10) >
convert(char(8), getdate(), 112)
THEN 'OP'
ELSE 'PB'
END
END

-- Final update of table left as exercise for the reader.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.