Also, since I am inserting more than one BOL at a time, how do I
autonumber the rows for each BOL number?
This will take a few more code snippets. You will need to trigger
something in the data. If the BOL is issued for separate order numbers,
you could use a reset for the autonum by saving the OrderNumber right
after the first fetch, before the first WHILE SQLCODE <> 100
and using a second variable inside the loop that returns the ordernumber
SET VAR vOrdNo1 TEXT
SET VAR vOrdNo2 TEXT
declare
open
fetch
SET VAR vOrdNo1 = .vOrdNum
set var vOrdNo2 = 0 -- so the comparison works properly
while sqlcode ...
if vOrdno2 <> .vOrdNo1 then
autonum col in table using 1,1 NONUM -- the NONUM will leave the
sequence of the other orders intact
while ... <> 0
insert ...
set var vpcs ...
select ordernumber into vOrdNo2 from tBOLLabels where count = insert
endwhile
-- at this point the variable vordno2 will have the order number you
just finished inserting.
endwhile
Albert Berry wrote:
My Bad, Jim - I left out the second FETCH command which has to be
repeated between the two ENDWHILE lines - see below. The cursor is
never advancing. to the next row. The first FETCH gets the data from
the first row, the second advances to the second row.
Albert
Jim Belisle wrote:
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
