1. I would try this for the heck of it:
OLD
DECLARE PTR1 CURSOR FOR SELECT +
Id,tDate,SvcCode,SvcDesc,Qty,SvcPr,SvcTot,SCS,Paid,tDOE +
FROM Transactions ORDER BY SCS=A, id, SvcCode &WhereClause
NEW
DECLARE PTR1 CURSOR FOR SELECT +
Id,tDate,SvcCode,SvcDesc,Qty,SvcPr,SvcTot,SCS,Paid,tDOE +
FROM Transactions &WhereClause ORDER BY SCS=A, id, SvcCode
I changed the order by and where clause to match the syntax.
2. Turn the whileopt off
3. Work with single quotes. The quote setting is a database setting. So if
you open another session or if another person connects, the database quotes
setting might change when this is running!! You need use the char function
to eliminate that possibility of error.
4. Predefine and clear your variables. This has caused many problems for me
in the past. You have some variables inside the cursors that are being
declared. This will also cause problems!!
I think one or more of these tips will take care of your problem.
I runs cursors all the time without error. The only times I have problems is
when I wrote something wrong.
Dan
>> (Note: the quotes change allows me to insert a text value for "Id"
using
>> the
>> 'text' format.)
>> SET QUOTES="
>> SET V WhereClause = +
>> ("Where id IS NOT NULL and Qty < 0 and SvcCode <> 10")
>> SET QUOTES='
>> DECLARE PTR1 CURSOR FOR SELECT +
>> Id,tDate,SvcCode,SvcDesc,Qty,SvcPr,SvcTot,SCS,Paid,tDOE +
>> FROM Transactions ORDER BY SCS=A, id, SvcCode &WhereClause
>> OPEN PTR1
>> SET V LastId TEXT = 'NONE'
>> FETCH PTR1 INTO +
>> oscId X1, osctDate X1, oscSvcCode X1, oscSvcDesc X1, oscQty X1, +
>> oscSvcPr X1, oscSvcTot X1, oscSCS X1, oscPaid X1, osctDOE X1
>> IF SQLCODE <> 100 THEN
>> SET V LoopStatus = 'RUN'
>> ELSE
>> SET V LoopStatus = 'STOP'
>> ENDIF
>> WHILE LoopStatus <> 'STOP' THEN
>> IF oscId = .LastId AND oscSCS = .LastSCS AND LoopStatus = 'RUN' THEN
>> -- this should NEVER happen but it does.
>> write 'error 1'
>> ENDIF
>> -- ... more code here
>> SET V LastSCS TEXT = .oscSCS
>> SET V LastId = .oscId
>> WHILE oscId = .LastId AND oscSCS = .LastSCS AND LoopStatus = 'RUN'
THEN
>> -- ... more code here
>> FETCH PTR1 INTO +
>> oscId X1, osctDate X1, oscSvcCode X1, oscSvcDesc X1, oscQty X1, +
>> oscSvcPr X1, oscSvcTot X1, oscSCS X1, oscPaid X1, osctDOE X1
>> IF SQLCODE <> 100 THEN
>> SET V LoopStatus = 'RUN'
>> ELSE
>> SET V LoopStatus = 'STOP'
>> ENDIF
>> IF oscId <> .LastId OR oscSCS <> .LastSCS OR LoopStatus <> 'RUN' THEN
>> write 'message 1, ok to exit exit loop'
>> ENDIF
>> ENDWHILE
>> ENDWHILE
>> DROP CURSOR PTR1
>> RETURN
