> -----Original Message-----
> From: Helen Borrie [mailto:helebor@...]
> Sent: 22 November 2001 08:40
> To: ib-support@yahoogroups.com; ib-support@yahoogroups.com
> Subject: RE: [ib-support] How to do a union?
>
>
> At 08:27 AM 22-11-01 +0200, Gerhardus Geldenhuis wrote:
>
> >What I basically need is a distinct select from
> "replication_log" giving me
> >all the table names and then I have a "lookup" table("replication_order")
> >that assigns a weight to the tablename to sort them because they
> must not be
> >sorted alphbetically. I also thought I needed a join but the
> manual referred
> >me to the union page.
> >If a join would solve the problem an example would be apprecaited.
> >
> >Here is typical data for the two tables
> >Replication_log
> >~~~~~~~~~~~~~~~
> >LOGID TABLENAME UID DBACTION BRANCH
> >===== ================= ================== ===========
> ============
> > 8 MTRL_FRAMES 882 UPDATE GLEN
> > 9 MTRL_FRAMES 882 UPDATE
> BOKSBURG
> > 10 MTRL_FRAMES 882 UPDATE GLEN
> > 11 MTRL_FRAMES 882 UPDATE
> BOKSBURG
> > 12 MTRL_FRAMES 890 INSERT GLEN
> > 13 MTRL_FRAMES 890 INSERT
> BOKSBURG
> > 14 MTRL_FRAMES 890 UPDATE GLEN
> > 15 MTRL_FRAMES 890 UPDATE
> BOKSBURG
> > 16 SUPPLIERS 34 UPDATE GLEN
> > 17 SUPPLIERS 34 UPDATE
> BOKSBURG
> >
> >Replication_order(would be constant most of the time)
> >TABLENAME WEIGHT
> >==============================
> >CUSTOMERS 1
> >MTRL_FRAMES 2
> >MTRL_GENERAL 3
> >MTRL_GLASSPRODUCTS 4
> >MTRL_MISCELLANEOUS 5
> >MTRL_MOUNTS 6
> >MTRL_PRINTS 7
> >MSFRAMES 8
> >MSMOUNTS 9
> >MSPRINTS 10
> >MULTIPLESUPPLIERS 11
> >POS_QISC 12
> >POS_QISC_ITEMS 13
> >POS_PAYMENTDETAIL 14
> >POS_MISC_STOCK 15
> >POS_WORKSORDER 16
> >POS_WORKSORDER_BOOKING 17
> >POS_BILLOFMATERIAL 18
>
> It's still not clear what you want to achieve by way of output.
> Do you want all of the rows in the log table ordered according to
> their order ("weight") in the replication_order table?
>
> If so, this will do it:
>
> select rl.tablename, ro.weight, ....whatever else you want...
> from replication_log rl
> join replication_order ro
> on rl.tablename = ro.tablename
> order by 2
>
> Beyond this, more info needed.
>

That is very almost what I want I will try to explain it again.

Scenario:
Replication Log
~~~~~~~~~~~~~~~
POS_BILLOFMATERIAL
POS_BILLOFMATERIAL
POS_BILLOFMATERIAL
MSFRAMES
MSMOUNTS
POS_MISC_STOCK
MTRL_GLASSPRODUCTS
MTRL_GLASSPRODUCTS
MTRL_GLASSPRODUCTS

I would want to do a distinct selection so I get the following result
MTRL_GLASSPRODUCTS
MSMOUNTS
POS_BILLOFMATERIAL
MSFRAMES
POS_MISC_STOCK

Then I would want to itterate through this list and do the replication table
by table.
First problem some tables need to be replicated before others so the list
needs to be
sorted in a specific way. I cant sort it alphabetically so I need to lookup
a
weight for the table to know how to sort it. Here is typically what I would
like to end up with after I have sorted it to weigth.

TableName Weight
MTRL_GLASSPRODUCTS 4
MSFRAMES 8
MSMOUNTS 9
POS_MISC_STOCK 15
POS_BILLOFMATERIAL 18

I hope this helps to explain better.

Thanks
Gerhardus