Albert,
Since this is the first time I have used a declare cursor, (and only
copied a WHILE loop once) it does not surprise me I did not get the
desired results. The below code is what I used and everything went well
until I got to the WHILE clause.
CREATE TEMP TABLE tBOLLabels +
(Lblitem INTEGER, BOLDate DATE, Control# TEXT 9, + OrderNumber TEXT 20,
+ Lblpcs INTEGER, PackageWgt REAL)
DECLARE cBollbl CURSOR FOR SELECT BOLDate, Control# +
OrderNumber, PackageQty, PackageWgt FROM BOLRows WHERE BOLDate =
.vBOLDate AND Shipdate IS NULL ORDER BY Control#
OPEN cBollbl
FETCH cBollbl INTO vBdate INDIC vi1, vCnum INDIC vi2, +
vOrdNum INDIC vi3, vPcs INDIC vi4, vPkgwgt INDIC vi5
-- Up to this point all was correct. The variables came up correctly.
This is the part where I get confused.
WHILE SQL <> 100 THEN
WHILE vPcs > 0 THEN
INSERT INTO tBollabels (BOLDate, Control#, OrderNumber, Lblpcs,
PackageWgt) + VALUES (.vBdate, .vCnum, .vOrdNum, '1', .vPkgwgt)
SET vPCS = (.VPCS - 1)
ENDWHILE
FETCH cBollbl INTO vBdate INDIC vi1, vCnum INDIC vi2, +
vOrdNum INDIC vi3, vPcs INDIC vi4, vPkgwgt INDIC vi5
ENDWHILE
Any help is appreciated.
Also, since I am inserting more than one BOL at a time, how do I
autonumber the rows for each BOL number?
Jim
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Albert
Berry
Sent: Monday, December 21, 2009 12:14 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: shipping labels
Jim, I would use a temporary table for this. I use temp tables for any
number of reports. When you have constructed the temporary labeling
table, all you need to do is LBLPRINT <your label name>. Once you have
constructed your command file, you can PUT it as a stored procedure, and
then when you want to print lables all you need to do is
SET VAR vShipNo INTEGER = <the shipment you want to print labels for>
CALL LblPrint (shipment number)
-- Add whatever other columns you need to print here, such as addresses,
shipment number etc.
DROP TABLE tmpBOL
CREATE TEMP TABLE tmpBOL ( +
Sequence INTEGER, Pieces INTEGER, TtlPieces INTEGER +
WeightEach INTEGER Weight Total INTEGER)
AUTONUM Sequence IN tmpBOL USING 1,1
-- This will sequentially number each row give you the 1 of, 2 of, etc.
The last one can be retrieved with
SELECT MAX Sequence INTO vTtlPieces FROM tmpBOL
and that gives you the number for the right side of the 1 of 6 on the
label.
Now insert Row one once, Row two once and Row three four times. You can
do this with a cursor
DECLARE c1 CURSOR FOR SELECT <col>,Pieces,<col>,<col> FROM <source
table>
OPEN c1
FETCH c1 INTO ....<var list>
WHILE SQLCODE <> 100 THEN -- end of data marker
-- This loop will add a row in the temp table for each piece
WHILE vPieces > 0 then
INSERT INTO tmpBOL <column list> VALUES (<var list>)
SET VAR vPieces = (.vPieces - 1)
ENDWHILE
ENDWHILE -- cursor that picks up the rows.
DROP CURSOR c1
Now you can use UPDATE to get the data in place. The customer,
attention, address, total pieces etc.
Now when you LBLPRINT the label that references this table, there will
be six rows of data, and will print six labels.
I hope this points you in an interesting direction.
Albert
Jim Belisle wrote:
Presently our Bill of lading program prints labels in this manner.
Let's say I have three rows of data for a bill of lading as below.
Pieces Wgt/pc total Wgt
Row one: 1 346 346
Row two: 1 845 845
Row three: 4 400 1600
The Program will print 6 labels; one each for the first two rows and
four for the third line.
Each label will show 1 of 6, 2 of 6, etc. and the weight as such
1 of 6 wgt 346
2 of 6 wgt 845
3 of 6 wgt 400
4 of 6 wgt 400
5 of 6 wgt 400
6 of 6 wgt 400
What function or command would I use to duplicate this process?
Jim
