At 09:40 PM 1/14/2012, TOM HART wrote:
I have the following that works, the pause 2 is only used to verify
that the cursor
is going thru the while, and it is.
select StoreNum into vStoreNum from StoreInfoTab where count = 1
DECLARE cursor1 CURSOR FOR SELECT StoreNum from AllStoreInfo where
storenum <> '12'
OPEN cursor1
FETCH cursor1 INTO zStoreNum INDICATOR zi1
WHILE sqlcode <>100 THEN
pause 2 using .zstorenum
select
StoreName,FolderOutName,FolderInName,StoreInventory,ChkInventory,StoreSales,+
ChkSales,StoreConsigner,ChkConsigner,StoreCustomer,ChkCustomer,StoreConPaid,ChkConPaid,+
StoreCustTrans,ChkCustTrans into vStoreName,vFolderOutName,vFolderInName,+
vStoreInventory,vChkInventory,vStoreSales,vChkSales,vStoreConsigner,vChkConsigner,+
vStoreCustomer,vChkCustomer,vStoreConPaid,vChkConPaid,vStoreCustTrans,vChkCustTrans+
from AllStoreInfo where StoreNum = .zStoreNum
*****this is where I put the updating routine, which works fine on
its own******
FETCH cursor1 INTO zStoreNum INDICATOR zi1
ENDWHILE
DROP CURSOR cursor1
return
I am wanting to use this to update information from remote
databases, so in the middle
of this I put my other update routine, which on its own works fine,
but when I put it
in the routine I get the error message about no endwhile,
endif. Like I said I have
just placed the routine in the fetch.
Tom,
Here are a few basic guidelines to adapt to your specific situation:
01. Carefully review your code and make sure that all variables are
pre-defined with
CORRECT data type as NULL before using the SELECT ... INTO, and
WHILE ... ENDWHILE
02. When using the SELECT ... INTO ..., always use INDICATOR
variable, which is used
to indicate if a null value is retrieved.
03. Before DECLARing the cursor, you may want to pre-qualify the
SELECT statement to
be used with DECLARE ccursorname CURSOR FOR SELECT .... command
04. Don't clear your WHILE variable(s).
05. Never CLEAR any variable(s) within the WHILE loop. You may set
and pre-define
variable as NULL, if necessary.
06. Don't define variables within your WHILE loop, only outside the
loop; values can
change within the loop.
A very basic example:
SET VAR vVar1 INTEGER = NULL
SET VAR vVar2 INTEGER = NULL
SET VAR vVar3 TEXT = NULL
SET VAR vRows INTEGER = NULL
SET VAR vRows = 0
SELECT COUNT(*) INTO vRows INDIC iv1 FROM AllStoreInfo WHERE
StoreNum <> '12'
IF vRows = 0 THEN
GOTO Done
ENDIF
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
DECLARE c1 CURSOR FOR SELECT column1, column2,column3 +
FROM AllStoreInfo WHERE StoreNum <> '12'
OPEN c1
FETCH c1 INTO +
vVar1 INDIC ivVar1, +
vVar2 INDIC ivVar2, +
vVar3 INDIC ivVar3
WHILE SQLCODE <> 100 THEN
-- Do what you have to do
SET VAR vVar1 = NULL
SET VAR vVar2 = NULL
SET VAR vVar3 = NULL
FETCH c1 INTO +
vVar1 INDIC ivVar1, +
vVar2 INDIC ivVar2, +
vVar3 INDIC ivVar3
ENDWHILE
DROP CURSOR c1
LABEL Done
CLS
CLEAR VARIABLES iv%,vV%,vRows
RETURN
Hope that provides you with some blue's clues!
Very Best R:egards,
Razzak.
