Fran
Below is a rewrite of your code with some comments about my changes. Below
that are more important questions about the logic.
-- Always DROP before declaring in case it was left open by interrupted
routine
-- or error in coding
DROP CURSOR Rt1
*(Declare Rt1 Scroll Cursor for select ProjID,Qt1, code, startDte, endDte
from
+
orderDtl order by code)
--IN this case you don't need a SCROLLable cursor because you are moving
--straight ahead, row by row. Use SCROLL only when you might need to
--move backwards, jump to a row,etc.
Declare Rt1 Cursor for select ProjID,Qt1, code, startDte, endDte from
+
orderDtl order by code
---------------------------------------------------------------
open Rt1
*(It's inefficient in your coding to have TWO Fetch commands, one inside the
loop and one outside
Fetch Rt1 into vProjID INDIC ivPJD, vQT1 INDIC ivQT1, vCODE INDIC ivCODE, +
vSTARTDTE INDIC iVSTARTDTE, vENDDTE INDIC ivENDDTE
)
-- Declare your variables and datatypes BEFORE the loop; types are guesses
-- on my part. Change to fit the datatypes of the corresponding columns
being
-- fetched
SET VAR vProjID INTEGER = NULL
SET VAR ivPJD INTEGER = NULL
SET VAR vQT1 INTEGER = NULL
SET VAR ivQT1 INTEGER = NULL
SET VAR vCode TEXT = NULL
SET VAR ivCode INTEGER = NULL
SET VAR vSTARTDTE DATE + NULL
SET VAR iVSTARTDTE INTEGER= NULL
SET VAR vENDDTE DATE = NULL
SET VAR ivENDDTE INTEGER = NULL
SET VAR vDATEchg DATE = NULL
-- What's the logic here? Is ORDERDTL a different table from ORDEDTL or a
typo?
SELECT MIN(STARTDTE)INTO vDATEchg FROM ORDERDTL
-----------------------------------------------------------------
--WHILE SQLCODE <>100 THEN
-- INstead set up an "infinite" loop, and use BREAK to leave the loop when
-- there are no more rows to process
-------------------------------------------------------------------
--LABEL REPEAT - this is unnecessary. A WHILE Loop will always return to
-- the first command after WHILE when it hits ENDWHILE
WHILE #PI <> 0 THEN
-- START with FETCH
Fetch Rt1 into vProjID INDIC ivPJD, vQT1 INDIC ivQT1, vCODE INDIC
ivCODE, +
vSTARTDTE INDIC iVSTARTDTE, vENDDTE INDIC ivENDDTE
-- Test THIS way for "no more rows to do"
IF SQLCODE = 100 THEN
-- "Break" goes to the line of code after ENDWHILE
BREAK
ENDIF
*(
SELECT SUM(QT1)INTO vQT1 FROM ORDEDTL WHERE vDATEchg BETWEEN vSTARTDTE AND
vENDDTE +
AND vCODE = CURRENT OF CURSOR
)
-- The above is likely to be slower than mine below and has a syntax error
-- anyway. It should be "CURRENT OF Rt1"
-- Note changes in WHERE clause. Form should be COLUMN = .Varname
-- The period in front of varname means "substitute the current value of
this
-- variable
SELECT SUM(QT1)INTO vQT1 FROM ORDEDTL WHERE DATEchg BETWEEN .vSTARTDTE
AND .vENDDTE +
AND CODE = .vCode
INSERT INTO EQUIP1 (PROJID, QT2, CODE, DATEUSED) #VALUES (.vPROJID, .vQT1,
.vCODE, .vDATEchg)
------------------------------------------------------------------
SET VAR vDATEchg = (.vDATEchg + 1)
*(Not needed - it happens by going to the start of the loop
Fetch NEXT FROM Rt1 into vProjID INDIC ivPJD, vQT1 INDIC ivQT1, vCODE INDIC
ivCODE, +
vSTARTDTE INDIC iVSTARTDTE, vENDDTE INDIC ivENDDTE
)
-- Not needed
-- GOTO REPEAT
ENDWHILE
DROP RT1
RETURN
This changes your code to a more efficient and correct form.
However, it doesn't solve two more important issues about the LOGIC of the
code.
1. Your DESCRIPTION makes it sound like there are MULTIPLE rows in OrderDtl
for each CODE value and that you want to sum the quantity for that Code.
I'm having trouble picturing what set of rows for Code = 'XXX' might look
like, and understanding the role of the column DateChg, but if there ARE
multiple rows, as suggested by the use of SELECT (SUM), then you might end
up with overlapping incorrect rows in EQUIP1.
For example, assuming that ORDERDTL and ORDEDTL are actually the same table
ProjID Qt1 code startDte endDte
1 10 XXX 1/1/05 1/31/05
2 20 XXX 1/2/05 1/30/05
Your code will produce these 2 rows in EQUIP1
PROJID QT2 CODE DATEUSED
1 30 XXX 1/1/05
2 20 XXX 1/1/05
Is this what you want?
2. Your code JUST ONCE finds vDateChg BEFORE the loop, as the earliest
date in the table, period. It then uses that date OVER AND OVER again in
loading rows into EQUIP1. Why? What is the business logic behind this?
David Blocker
[EMAIL PROTECTED]
781-784-1919
Fax: 781-784-1860
Cell: 339-206-0261
----- Original Message -----
From: "Fran Yount" <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[email protected]>
Sent: Sunday, February 06, 2005 10:18 PM
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]
>
