WHERE COUNT = INSERT is a SQL phrase that refers the engine to the last
row you inserted. The idea is to get the number you just inserted into a
variable so that you can compare it to the number retrieved by the FETCH
command, and do something (reset the autonumber to start again at 1) if
the control number has changed.
The variable I called vpcs is quite simply the number of pieces in the
BOL for that particular control (Part?). In your first example you had a
single BOL number for three items shipped. The first and second had only
one box each, the third had four boxes. The vpcs segment of the code
keeps inserting the particular item until all pieces have been inserted.
If you have four pieces, it will insert, tell itself "3 to go", insert
again, decrement vpcs and see "2 to go", etc. When vpcs = 0, you have
inserted all four rows you need to print package labels for the four
pieces and the system exits the WHILE loop and fetches the next item in
your source table.
Perhaps blocking it out for you will help you see what is going on. I
think you might be getting lost in real code, and not comprehending
fully what is going on. I think perhaps you should set up a decision
tree using Visio or similar and figure out what is going on that
way.Here is what I am thinking while I attempt to answer your questions.
The source data would be your first example, that I have sketched here in
* Control number 1001, 6 boxes. We need 6 labels
o Part A, 1 box
o Part B, 1 box
o Part C, 4 boxes
* Control number 1002
o etc.
* We want to label the boxes for 1001 in this fashion: [1 of 6] [2
of 6] ... [6 of 6]
* Autonumber a column and reset it to start at 1 when the control
number changes.
* The 1, 2, 3, 4, 5, 6 numbers are found from the autonumber column
* The 6 in "1 of 6" is found by looking up the highest box number
for each control number. We could count rows instead.
* In order to have a label for each of the four boxes for part C, we
need to somehow print four labels if there are four boxes for part C.
* We do this by pulling the number of boxes from the table, (FETCH
... into vpcs)
* Insert the first row, decrement vpcs by 1, then the second row,
until all 4 boxes are in the table. (WHILE vpcs > 0)
* Part A, we start with one piece, so it goes to zero as soon as the
first label data is inserted, and we move to part B.
* We use a cursor to simply walk down the source data until we run
out. (WHILE SQLCODE <> 100). If SQLCODE, a system value, equals
100, we have no more data to insert.
The DECLARE, OPEN, FETCH, WHILE are just how we tell R:Base to create
the label rows in the temporary table. Always start your coding with
English. Convert to SQL/R:Base later.
Albert
Jim Belisle wrote:
Albert,
You and John have been very helpful to me.
I am still working on inserting rows into a temp tables and auto
numbering the rows based on the Control# field. This is for multiple
BOL in the table. I am trying to incorporate your suggestions. I think
I figured things out up to getting the inserted Control# into the second
var then starting the counting all over again.
Here is what I have done. I put NEW next to your suggestions.
SET VAR vBOLDate DATE = 12/23/2009
SET VAR vbdate DATE = NULL
SET VAR vcnum TEXT = NULL
SET VAR vordnum TEXT = NULL
SET VAR vpcs INTEGER = 0
SET VAR vpkgwgt REAL = NULL
SET VAR vlblitem INTEGER = 0
SET VAR vCNumber1 TEXT = NULL (NEW)
SET VAR vCNumber2 TEXT = NULL (NEW)
SET ERROR MESSAGE 2038 OFF
DROP TABLE tbollabels
SET ERROR MESSAGE 2038 ON
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
OPEN cbollbl
FETCH cbollbl INTO vbdate INDIC vi1, vcnum INDIC vi2, vordnum INDIC vi3,
+
vpcs INDIC vi4, vpkgwgt INDIC vi5
SET VAR vCNumber1 = .vCnum (NEW)
SET VAR vCNumber2 = 0 (NEW)
WHILE SQLCODE <> 100 THEN
IF vCNumber2 <> vCNumber1 THEN (NEW)
AUTONUM lblitem IN tbollabels USING 1 1 NONUM (NEW)
WHILE vpcs > 0 THEN
INSERT INTO tbollabels (boldate, control#, ordernumber, lblpcs,
packagewgt) VALUES (.vbdate, .vcnum, .vordnum,1, .vpkgwgt)
SET VAR vpcs = (.vpcs - 1)
ENDIF (NEW)
(I stopped here)
ENDWHILE
FETCH cbollbl INTO vbdate INDIC vi1, vcnum INDIC vi2, vordnum INDIC
vi3, +
vpcs INDIC vi4, vpkgwgt INDIC vi5
ENDWHILE
DROP CURSOR cbollbl
Here is my question.
If what I have done looks like what you suggested below, how do I do the
count line?
select Control# into vOrdNo2 from tBOLLabels where count = insert
What is count = insert?
Jim
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Albert
Berry
Sent: Thursday, December 24, 2009 5:50 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: shipping labels
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:
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Albert
Berry
Sent: Thursday, December 24, 2009 5:50 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: shipping labels
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
--- RBASE-L
=======================3D=======================3
D=
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
=======================3D=======================3
D=
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
=======================3D=======================3
D=
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
=======================3D=======================3
D=
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
=======================3D=======================3
D=
