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=
