In that case use this code.
SET ERROR MESSAGES ON
SET VAR vBOLDate = 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 vAutoNumberCol INTEGER = 1
DROP TABLE tBOLLabels
CREATE TEMP TABLE tBOLLabels (Lblitem INTEGER, BOLDate DATE, Control# TEXT
9,+
OrderNumber TEXT 20, Lblpcs INTEGER, PackageWgt REAL,AutoNumberCol
Integer)
DECLARE cBollbl CURSOR FOR SELECT BOLDate, Control#, OrderNumber,
PackageQty, PackageWgt +
FROM BOLRows WHERE BOLDate = .vBOLDate
OPEN cBollbl
FETCH cBollbl INTO vBdate INDI vi1, vCnum INDI vi2, vOrdNum INDI vi3,vPcs
INDI vi4, vPkgwgt INDI vi5
WHILE SQLCODE <> 100 THEN
WHILE vPcs > 0 THEN
INSERT INTO tBollabels (BOLDate, Control#, OrderNumber,
Lblpcs,PackageWgt,AutoNumberCol) +
VALUES (.vBdate, .vCnum, .vOrdNum,1, .vPkgwgt,.vAutoNumberCol)
SET VAR vPCS = (.VPCS - 1)
SET VAR vAutoNumberCol = (.vAutoNumberCol + 1)
ENDWHILE
FETCH cBollbl INTO vBdate INDI vi1, vCnum INDI vi2, vOrdNum INDI vi3,vPcs
INDI vi4, vPkgwgt INDI vi5
SET VAR vAutoNumberCol = 1
ENDWHILE
Drop cursor cBollbl
Brow * from tBOLLabels
RETURN
John
From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle
Sent: Thursday, December 24, 2009 9:24 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: shipping labels
John,
Your code worked when I just clicked the run line of the command file.
Before, I was checking the code in the edit mode of the Command files
section of my database.
Could that have caused the error?
I am sure I missed some simple item.
Since I have multiple Bill Of Ladings that I insert into this table at one
time, I will need to autonumber based on the field Control#.
That way each Control# has each row start with 1.
As I mentioned to Albert, this is all new ground for me. I appreciate your
help along with Alberts.
Jim
From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer
Sent: Thursday, December 24, 2009 8:06 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: shipping labels
Jim,
Here is working code. I tested it. Sometimes when you cut and paste code
to the list the formatting gets messed up. If it does not work, send me
your Email address and I will send you the .RMD.
SET ERROR MESSAGES ON
SET VAR vBOLDate = 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
DROP TABLE tBOLLabels
CREATE TEMP TABLE tBOLLabels (Lblitem INTEGER, BOLDate DATE, Control# TEXT
9,+
OrderNumber TEXT 20, Lblpcs INTEGER, PackageWgt REAL,AutoNumberCol
Integer)
AUTONUM AutoNumberCol IN tBOLLabels USING 1 1 NUM
DECLARE cBollbl CURSOR FOR SELECT BOLDate, Control#, OrderNumber,
PackageQty, PackageWgt +
FROM BOLRows WHERE BOLDate = .vBOLDate
OPEN cBollbl
FETCH cBollbl INTO vBdate INDI vi1, vCnum INDI vi2, vOrdNum INDI vi3,vPcs
INDI vi4, vPkgwgt INDI vi5
WHILE SQLCODE <> 100 THEN
WHILE vPcs > 0 THEN
INSERT INTO tBollabels (BOLDate, Control#, OrderNumber,
Lblpcs,PackageWgt) +
VALUES (.vBdate, .vCnum, .vOrdNum,1, .vPkgwgt)
SET VAR vPCS = (.VPCS - 1)
ENDWHILE
FETCH cBollbl INTO vBdate INDI vi1, vCnum INDI vi2, vOrdNum INDI vi3,vPcs
INDI vi4, vPkgwgt INDI vi5
ENDWHILE
Drop cursor cBollbl
Brow * from tBOLLabels
RETURN
John
Email: [email protected]
