SQL Anywhere database

Author

SQL How to

Edgard L. Riba

2005-08-20, 8:23 pm

Hi,

I need some help in getting this going...

I have two tables:

CREATE TABLE iTransRow (
idLoc TINYINT NOT NULL,
idSeq INTEGER NOT NULL,
idLine INTEGER NOT NULL,
Item INTEGER NULL,
ItemExt VARCHAR(14) NULL,
PieceCount DECIMAL(7,2) NULL,
Pack DECIMAL(8,3) NULL,
InvoiceQty DECIMAL(11,5) NULL,
Qty DECIMAL(11,5) NULL,
Cost DECIMAL(8,5) NULL,
SuggestedPrice DECIMAL(8,5) NULL,
Price DECIMAL(8,5) NULL,
ListPrice DECIMAL(8,5) NULL,
Tax DECIMAL(9,4) NULL,
TaxPercent DECIMAL(9,4) NULL,
TaxGroup TINYINT NULL,
Discounts DECIMAL(9,4) NULL,
Expenses DECIMAL(9,4) NULL,
OtherTaxes DECIMAL(9,4) NULL,
Freight DECIMAL(9,4) NULL,
TransactionDate DATETIME NULL,
Notes TINYINT NULL,
Status TINYINT NULL,
PRIMARY KEY (idLoc,idSeq,idLine)

);

And a second identical table

CREATE TABLE iTrans2Row (
idLoc TINYINT NOT NULL,
idSeq INTEGER NOT NULL,
idLine INTEGER NOT NULL,
Item INTEGER NULL,
ItemExt VARCHAR(14) NULL,
PieceCount DECIMAL(7,2) NULL,
Pack DECIMAL(8,3) NULL,
InvoiceQty DECIMAL(11,5) NULL,
Qty DECIMAL(11,5) NULL,
Cost DECIMAL(8,5) NULL,
SuggestedPrice DECIMAL(8,5) NULL,
Price DECIMAL(8,5) NULL,
ListPrice DECIMAL(8,5) NULL,
Tax DECIMAL(9,4) NULL,
TaxPercent DECIMAL(9,4) NULL,
TaxGroup TINYINT NULL,
Discounts DECIMAL(9,4) NULL,
Expenses DECIMAL(9,4) NULL,
OtherTaxes DECIMAL(9,4) NULL,
Freight DECIMAL(9,4) NULL,
TransactionDate DATETIME NULL,
Notes TINYINT NULL,
Status TINYINT NULL,
PRIMARY KEY (idLoc,idSeq,idLine)

);

I need to write a stored procedure to synchronize all the rows from
iTransRow to iTrans2Row for a given idLoc and idSeq. By synchronize I mean
(can be done in any order):
1) Delete all rows in iTrans2Row that are not in iTransRow,
2) Update all rows in iTrans2Row which have different data in iTransRow,
3) Insert all rows which are in iTransRow but are not in iTrans2Row.

I need to do this for about 1200 transactions per day (a transaction is
given by an
IDLoc/IDSeq pair), and each transaction may have up to about 25000 rows.

Could someone with more experience help me out with this?

Thanks in advance,
Edgard

Richard Biffl

2005-08-20, 8:23 pm

I'd try this first, and get fancier only if performance was unacceptable:

CREATE PROCEDURE SyncTransRows (IN loc TINYINT, IN seq INTEGER)
BEGIN

DELETE FROM iTrans2Row
WHERE idLoc = loc AND idSeq = seq;

INSERT INTO iTrans2Row WITH AUTO NAME
SELECT * FROM iTrans
WHERE idLoc = loc AND idSeq = seq;

END;

You might want to keep the tables synchronized with triggers. You might also
look at why you need a second table at all, if it merely duplicates the
first table.

Richard

"Edgard L. Riba" <elriba at rimith dot com> wrote in message
news:4307a9b6$1@foru
ms-1-dub...
> Hi,
>
> I need some help in getting this going...
>
> I have two tables:
>
> CREATE TABLE iTransRow (
> idLoc TINYINT NOT NULL,
> idSeq INTEGER NOT NULL,
> idLine INTEGER NOT NULL,
> Item INTEGER NULL,
> ItemExt VARCHAR(14) NULL,
> PieceCount DECIMAL(7,2) NULL,
> Pack DECIMAL(8,3) NULL,
> InvoiceQty DECIMAL(11,5) NULL,
> Qty DECIMAL(11,5) NULL,
> Cost DECIMAL(8,5) NULL,
> SuggestedPrice DECIMAL(8,5) NULL,
> Price DECIMAL(8,5) NULL,
> ListPrice DECIMAL(8,5) NULL,
> Tax DECIMAL(9,4) NULL,
> TaxPercent DECIMAL(9,4) NULL,
> TaxGroup TINYINT NULL,
> Discounts DECIMAL(9,4) NULL,
> Expenses DECIMAL(9,4) NULL,
> OtherTaxes DECIMAL(9,4) NULL,
> Freight DECIMAL(9,4) NULL,
> TransactionDate DATETIME NULL,
> Notes TINYINT NULL,
> Status TINYINT NULL,
> PRIMARY KEY (idLoc,idSeq,idLine)

> );
>
> And a second identical table
>
> CREATE TABLE iTrans2Row (
> idLoc TINYINT NOT NULL,
> idSeq INTEGER NOT NULL,
> idLine INTEGER NOT NULL,
> Item INTEGER NULL,
> ItemExt VARCHAR(14) NULL,
> PieceCount DECIMAL(7,2) NULL,
> Pack DECIMAL(8,3) NULL,
> InvoiceQty DECIMAL(11,5) NULL,
> Qty DECIMAL(11,5) NULL,
> Cost DECIMAL(8,5) NULL,
> SuggestedPrice DECIMAL(8,5) NULL,
> Price DECIMAL(8,5) NULL,
> ListPrice DECIMAL(8,5) NULL,
> Tax DECIMAL(9,4) NULL,
> TaxPercent DECIMAL(9,4) NULL,
> TaxGroup TINYINT NULL,
> Discounts DECIMAL(9,4) NULL,
> Expenses DECIMAL(9,4) NULL,
> OtherTaxes DECIMAL(9,4) NULL,
> Freight DECIMAL(9,4) NULL,
> TransactionDate DATETIME NULL,
> Notes TINYINT NULL,
> Status TINYINT NULL,
> PRIMARY KEY (idLoc,idSeq,idLine)

> );
>
>
> I need to write a stored procedure to synchronize all the rows from
> iTransRow to iTrans2Row for a given idLoc and idSeq. By synchronize I
mean
> (can be done in any order):
> 1) Delete all rows in iTrans2Row that are not in iTransRow,
> 2) Update all rows in iTrans2Row which have different data in iTransRow,
> 3) Insert all rows which are in iTransRow but are not in iTrans2Row.
>
> I need to do this for about 1200 transactions per day (a transaction is
> given by an
> IDLoc/IDSeq pair), and each transaction may have up to about 25000 rows.
>
> Could someone with more experience help me out with this?
>
> Thanks in advance,
> Edgard
>
>
>

Edgard L. Riba

2005-08-23, 1:23 pm

Hi Richard,

Thanks for the input.

Edgard

"Richard Biffl" < sybaseforums@removve
thiisphrraseblacklet
tersoftware.com>
escribió en el mensaje news:4307b55a$1@foru
ms-1-dub...
> I'd try this first, and get fancier only if performance was unacceptable:
>
> CREATE PROCEDURE SyncTransRows (IN loc TINYINT, IN seq INTEGER)
> BEGIN
>
> DELETE FROM iTrans2Row
> WHERE idLoc = loc AND idSeq = seq;
>
> INSERT INTO iTrans2Row WITH AUTO NAME
> SELECT * FROM iTrans
> WHERE idLoc = loc AND idSeq = seq;
>
> END;
>
> You might want to keep the tables synchronized with triggers. You might
> also
> look at why you need a second table at all, if it merely duplicates the
> first table.
>
> Richard
>
>
> "Edgard L. Riba" <elriba at rimith dot com> wrote in message
> news:4307a9b6$1@foru
ms-1-dub...
> mean
>
>

Sponsored Links