Albert,
I got this ERROR message:
-ERROR- Column or variable SQL not found (2515)
Here is my code. Am I missing a Plus sign or something?
-- EEP for Inserting rows into temp table.
SET VAR vBOLDate = 12/23/2009
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
OPEN cBollbl
FETCH cBollbl INTO vBdate INDIC vi1, vCnum INDIC vi2, vOrdNum INDIC vi3,
+
vPcs INDIC vi4, vPkgwgt INDIC vi5
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
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Albert
Berry
Sent: Thursday, December 24, 2009 5:22 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: shipping labels
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
>>
>>
>
>
>
>
