Fran
Here's a snippet that I use to fill a temp table with cost of materials
based on an individual formula for each row. It uses two cursors- one
to fetch the part numbers that need pricing, and another to fetch the
components of the part numbers to calculate the price.
An important thing is to declare all variables before your while loop.
In your example, you do not need the repeat LABEL / GOTO. The WHILE
statement automatically loops until it SQLCODE = 100.
-----------------------------
DECLARE cnumcol CURSOR FOR SELECT numcol FROM qcolors WHERE quotenum =
.vquotenum
OPEN cnumcol
FETCH cnumcol INTO vnumcol INDIC ivnumcol
WHILE SQLCODE <> 100 THEN
SET VAR vcoldesc = coldesc IN colornumbers WHERE numcol = .vnumcol
SET VAR vloading = newloading IN colornumbers WHERE numcol = .vnumcol
IF vnumcol CONTAINS '000' THEN
SET VAR vtotprice = materialcost IN inkcost WHERE numcol = .vnumcol
ELSE
SET VAR vsumratio = (SUM(partratio)) IN colorformulas WHERE numcol =
.vnumcol
SET VAR vsumoil = (SUM(oilratio)) IN colorformulas WHERE numcol =
.vnumcol
SET VAR voil = (1 / (.vloading + 1))
SET VAR vpig = (IFEQ(.vsumoil,0,1,(1-.voil)))
SET VAR vmult = (.vpig / .vsumratio)
DECLARE c1 CURSOR FOR SELECT numcol, pigment, partratio, oilratio
FROM colorformulas WHERE numcol = .vnumcol ORDER BY pigorder
OPEN c1
FETCH c1 INTO vformulanum INDIC ivformula, vpigment INDIC ivpigment,
vpigratio INDIC ivpig, voilratio INDIC ivoil
WHILE SQLCODE <> 100 THEN
SET VAR vratio = (IFEQ(.vpigratio, NULL, .voilratio, .vpigratio))
SET VAR vpigmath = (.vpigratio * .vmult)
SET VAR voilmath = (.voil * (.voilratio / 100))
SET VAR vpartmath = (IFEQ(.vpigratio, NULL, .voilmath, .vpigmath))
SET VAR vprice = costprice IN inventory WHERE partnum = .vpigment
SET VAR vunit = units IN inventory WHERE partnum = .vpigment
IF vunit='LBS' THEN
SET VAR vfactor=(1/452.48)
ELSE
IF vunit='GAL' THEN
SET VAR vlbgal = lbgal IN inventory WHERE partnum=.vpigment
SET VAR vfactor=(1/(.vlbgal*452.48))
ELSE
IF vunit='TROY OZ' THEN
SET VAR vfactor=(1/31.1)
ELSE
IF vunit ='GRAMS' THEN
SET VAR vfactor=1
ELSE
SET VAR vfactor=(1/1000)
ENDIF
ENDIF
ENDIF
ENDIF
SET VAR vprice = (.vprice * .vfactor)
SET VAR vpartprice = (.vpartmath * .vprice)
SET VAR vtotprice = (.vtotprice + .vpartprice)
FETCH c1 INTO vformulanum INDICivformula, vpigment INDIC
ivpigment, vpigratio INDIC ivpig, voilratio INDIC ivoil
ENDWHILE
DROP CURSOR c1
ENDIF
INSERT INTO tcolorcost numcol, coldesc, materialcost VALUES (.vnumcol,
.vcoldesc, .vtotprice)
SET VAR vtotprice DOUBLE = NULL
FETCH cnumcol INTO vnumcol INDIC ivnumcol
ENDWHILE
DROP CURSOR cnumcol
-----Original Message-----
From: Fran Yount [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 06, 2005 10:18 PM
To: RBG7-L Mailing List
Subject: [RBG7-L] - Fetch command
I have a table that has various pieces of equipment in listed by code
and use dates in it. I am trying to come up with a declare Cursor and
fetch command that will let me add the sum of the pieces needed for the
various dates and store each day in the table then move to the next code
or piece of equipment and do the same. I have not done this before so
any help would be greatly appreciated. Here is what I have but not
tested. Or if you have if you have some code files I could look though.
Declare Rt1 Scroll Cursor for select ProjID,Qt1, code, startDte, endDte
from
+
orderDtl order by code
---------------------------------------------------------------
open Rt1
Fetch Rt1 into vProjID INDIC ivPJD, vQT1 INDIC ivQT1, vCODE INDIC
ivCODE, + vSTARTDTE INDIC iVSTARTDTE, vENDDTE INDIC ivENDDTE
SET VAR vDATEchg DATE = NULL
SELECT MIN(STARTDTE)INTO vDATEchg FROM ORDERDTL
-----------------------------------------------------------------
WHILE SQLCODE <>100 THEN
-------------------------------------------------------------------
LABEL REPEAT
SELECT SUM(QT1)INTO vQT1 FROM ORDEDTL WHERE vDATEchg BETWEEN vSTARTDTE
AND vENDDTE +
AND vCODE = CURRENT OF CURSOR
INSERT INTO EQUIP1 (PROJID, QT2, CODE, DATEUSED) #VALUES (.vPROJID,
.vQT1, .vCODE, .vDATEchg)
------------------------------------------------------------------
SET VAR vDATEchg + (.vDATEchg + 1)
Fetch NEXT FROM Rt1 into vProjID INDIC ivPJD, vQT1 INDIC ivQT1, vCODE
INDIC ivCODE, + vSTARTDTE INDIC iVSTARTDTE, vENDDTE INDIC ivENDDTE
GOTO REPEAT
ENDWHILE
DROP RT1
RETURN
Sincerely,
Fran Yount
[EMAIL PROTECTED]
